27,579
社区成员
发帖
与我相关
我的任务
分享
create table AAA1
(
ID int, --主键
订单号 nvarchar(50),
颜色 nvarchar(50),
尺码 nvarchar(50) ,
数量 int ,
)
go
insert into AAA1 values(1,'A001','黑色','M',10)
insert into AAA1 values(2,'A001','白色','M',20)
insert into AAA1 values(3,'A001','黑色','L',30)
insert into AAA1 values(4,'A001','兰色','L',40)
insert into AAA1 values(5,'A001','白色','XL',50)
insert into AAA1 values(6,'A002','黑色','M',10)
insert into AAA1 values(7,'A002','白色','M',20)
insert into AAA1 values(8,'A002','黑色','L',30)
insert into AAA1 values(9,'A003','兰色','L',40)
insert into AAA1 values(10,'A003','白色','XL',50)
SELECT
*,
DENSE_RANK() OVER (PARTITION BY 订单号
ORDER BY
颜色
) 颜色编码,
DENSE_RANK() OVER (PARTITION BY 订单号
ORDER BY
尺码
) 尺码编码
FROM
AAA1
ORDER BY
ID;
SELECT
*,
DENSE_RANK() OVER (PARTITION BY 订单号
ORDER BY
CASE WHEN 颜色='黑色' THEN 1
WHEN 颜色='白色' THEN 2
WHEN 颜色='兰色' THEN 3
end
) 颜色编码,
DENSE_RANK() OVER (PARTITION BY 订单号
ORDER BY
CASE WHEN 尺码='M' THEN 1
WHEN 尺码='L' THEN 2
WHEN 尺码='XL' THEN 3
end
) 尺码编码
FROM
AAA1
ORDER BY
ID;
SELECT
*,
DENSE_RANK() OVER (
ORDER BY
CASE WHEN 颜色='黑色' THEN 1
WHEN 颜色='白色' THEN 2
WHEN 颜色='兰色' THEN 3
end
) 颜色编码,
DENSE_RANK() OVER (
ORDER BY
CASE WHEN 尺码='M' THEN 1
WHEN 尺码='L' THEN 2
WHEN 尺码='XL' THEN 3
end
) 尺码编码
FROM
AAA1
ORDER BY
ID;
SELECT
*,
DENSE_RANK() OVER (
ORDER BY
颜色
) 颜色编码,
DENSE_RANK() OVER (
ORDER BY
尺码
) 尺码编码
FROM
AAA1
ORDER BY
ID;