shenqy2020 2022-10-06 16:11 采纳率: 100%
浏览 61
已结题

不太懂sql语句中的游标使用,请问一下你们怎么写的

根据下面的代码,在事实表中采用游标插入数据
--1、创建维度表


/1.1 商店维表/
CREATE TABLE DIM_STORE
( STORE_ID VARCHAR(8) primary key,
DETIL_STORE VARCHAR(20))

/1.2 点单方式/
CREATE TABLE DIM_DIANDAN
(DIANDAN_ID int primary key,
DETIL_DIANDAN VARCHAR(15))

/1.3 商品类维表/
CREATE TABLE DIM_GOODS
(GOODS_ID VARCHAR(8) primary key,
GOODS_MING VARCHAR(50))

/1.4 订单日期/
CREATE TABLE DIM_DATE
(TIME_ID VARCHAR(8)primary key,
TIME_MONTH VARCHAR(6),
TIME_YEAR VARCHAR(6),
TINE_QUAUTER VARCHAR(8),
TIME_WEEK VARCHAR(6),
TIME_XUN VARCHAR(4))

/1.5 顾客/
CREATE TABLE DIM_CUSTOMER
(CUSTOMER_ID varchar(8) primary key,
SEX_CUSTOMER VARCHAR(2))

/1.6 年龄/
CREATE TABLE DIM_MIANLING
(NIANLING_ID int primary key,
MAX_NIAN INT,
MIN_NIAN INT)

/1.7 订单价值/
CREATE TABLE DIM_ORDERVALUES
(ORDER_VALUES_ID INT primary key,
DSC VARCHAR(30),
MIN_VALUE INT,
MAX_VALUE INT)

/1.8优惠券/
CREATE TABLE DIM_YOUHUI
(YOUHUI_ID INT PRIMARY KEY,
USE_YOUHUI VARCHAR(4))

/1.9带走方式/
CREATE TABLE DIM_BRING
(BRING_ID INT PRIMARY KEY,
DETIL_BRING VARCHAR(4))

INSERT INTO DIM_STORE VALUES ('1002','益禾堂')
INSERT INTO DIM_STORE VALUES ('1003','古茗')
INSERT INTO DIM_STORE VALUES ('1004','茶百道')
INSERT INTO DIM_STORE VALUES ('1005','CoCo')
INSERT INTO DIM_STORE VALUES ('1006','喜茶')
INSERT INTO DIM_STORE VALUES ('1007','奈雪的茶')
INSERT INTO DIM_STORE VALUES ('1008','霸王茶姬')
INSERT INTO DIM_STORE VALUES ('1009','蜜雪冰城')

INSERT INTO DIM_DIANDAN VALUES(0,'线上')
INSERT INTO DIM_DIANDAN VALUES(1,'线下')

INSERT INTO DIM_GOODS VALUES('4001','果茶')
INSERT INTO DIM_GOODS VALUES('4002','奶茶')
INSERT INTO DIM_GOODS VALUES('4003','原茶')
INSERT INTO DIM_GOODS VALUES('4004','咖啡')

INSERT INTO DIM_MIANLING VALUES(1,15,10)
INSERT INTO DIM_MIANLING VALUES(2,20,15)
INSERT INTO DIM_MIANLING VALUES(3,25,20)
INSERT INTO DIM_MIANLING VALUES(4,30,25)
INSERT INTO DIM_MIANLING VALUES(5,35,30)
INSERT INTO DIM_MIANLING VALUES(6,40,35)
INSERT INTO DIM_MIANLING VALUES(7,45,40)
INSERT INTO DIM_MIANLING VALUES(8,50,45)
INSERT INTO DIM_MIANLING VALUES(9,55,50)
INSERT INTO DIM_MIANLING VALUES(10,60,55)

INSERT INTO DIM_ORDERVALUES VALUES(1,'0-30',0,30 )
INSERT INTO DIM_ORDERVALUES VALUES(2,'30-60',30,60 )
INSERT INTO DIM_ORDERVALUES VALUES(3,'60-90',60,90 )
INSERT INTO DIM_ORDERVALUES VALUES(4,'90-120',90,120 )
INSERT INTO DIM_ORDERVALUES VALUES(5,'120-200',120,200 )
INSERT INTO DIM_ORDERVALUES VALUES(6,'200以上',200,1000000000 )

declare @day dateTIME
SET @day='2001-01-01'
while @day<'2005-01-01'
BEGIN
insert into DIM_DATE
SELECT CONVERT(CHAR(8),@day,112),--112表示日期格式为yymmdd, char(8)表示将日期类型转换为长度为的字符类型。
CONVERT(CHAR(6),@day,112),--表示取年月的值,例如‘’
CONVERT(CHAR(4),@day,112)+'年',--表示取年份的值,例如‘年’
'第'+CAST(DATEname(QUARTER , @day) AS VARCHAR(1))+'季度',--datename(datepart,date)功能是返回指定日期的指定字符串
DATEname(weekday , @day),
case WHEN DATEPART(DAY,@day)<11 THEN '上旬' WHEN DATEPART(DAY,@day)<21 THEN '中旬' ELSE '下旬' END
SELECT @day=DATEADD(DAY,1,@day)
END

INSERT INTO DIM_CUSTOMER
SELECT CustomerID,CUSTOMERTYPE
FROM AdventureWorks.Sales.Customer

INSERT INTO DIM_YOUHUI VALUES(1,'YES')
INSERT INTO DIM_YOUHUI VALUES(2,'NO')

INSERT INTO DIM_YOUHUI VALUES(1,'堂食')
INSERT INTO DIM_YOUHUI VALUES(1,'打包')

--三、建事实表(存放订单的订单号、订单时间、订单状态、下订单方式、客户ID、销售人ID、
--订货方式、订单价值段、销售额、税、运费)


CREATE TABLE FACT_SALEORDER(
SALEORDERID INT primary key,
TIME_ID VARCHAR(8),
STORE_ID VARCHAR(8),
DIANDAN_ID int,
GOODS_ID VARCHAR(8),
CUSTOMER_ID varchar(8),
NIANLING_ID int,
ORDER_VALUES_ID INT,
YOUHUI_ID INT,
BRING_ID INT,
SUBTOTAL DECIMAL(10,2))

TRUNCATE TABLE FACT_SALEORDER --删除中的所有行,而不记录单个行删除操作
INSERT INTO FACT_SALEORDER
SELECT

  • 写回答

1条回答 默认 最新

    报告相同问题?

    相关推荐 更多相似问题

    问题事件

    • 系统已结题 10月14日
    • 已采纳回答 10月6日
    • 创建了问题 10月6日

    悬赏问题

    • ¥60 有关C#的问题,基础牢固的来
    • ¥15 Python算法设计Seam carving--让图片比例随心缩放
    • ¥15 页面制作,HTML CSS
    • ¥15 通个批处理实现 win7和win10 修改或设置:显示操作系统列表时间为2秒
    • ¥20 报错:Unsupported class file major version 59
    • ¥15 用ASP代码实现跨站脚本攻击
    • ¥50 ABP中怎样接收vue上传的图片
    • ¥15 uniapp设置音频的倍速播放无效
    • ¥15 关于#hc#的问题,如何解决?
    • ¥15 cyclegan跨模态生成