27,579
社区成员
发帖
与我相关
我的任务
分享
product表
id cod
1 BB0449-026
t_ma_sc_sku_relationb表
sku_id sku_category_id
1 1
1 2
1 11
1 21
1 31
t_ma_sku_category表
id name
1 男子
2 女子
11 服装
21 运动休闲
31 训练
现在此处需要这样的信息
code category1 category2 category3 category4
BB0449-026 无区分 服装 运动休闲 训练
求sql?
CREATE TABLE #product
(
id VARCHAR(20) ,
cod VARCHAR(20)
)
INSERT INTO #product
( id, cod )
VALUES ( '1', 'BB0449-026' )
CREATE TABLE #t_ma_sc_sku_relationb
(
sku_id VARCHAR(20) ,
sku_category_id VARCHAR(20)
)
INSERT INTO #t_ma_sc_sku_relationb
SELECT '1' ,
'1'
UNION ALL
SELECT '1' ,
'2'
UNION ALL
SELECT '1' ,
'11'
UNION ALL
SELECT '1' ,
'21'
UNION ALL
SELECT '1' ,
'31'
CREATE TABLE #t_ma_sku_category
(
id VARCHAR(20) ,
name VARCHAR(20)
)
INSERT INTO #t_ma_sku_category
SELECT '1 ' ,
'男子 '
UNION ALL
SELECT '2 ' ,
'女子 '
UNION ALL
SELECT '11 ' ,
'服装 '
UNION ALL
SELECT '21 ' ,
'运动休闲'
UNION ALL
SELECT '31 ' ,
'训练'
SELECT A.cod ,
MAX(CASE WHEN B.sku_category_id = 1
OR B.sku_category_id = 2 THEN '无分区'
END) AS category1 ,
MAX(CASE WHEN B.sku_category_id = 11 THEN NAME
END) AS category2 ,
MAX(CASE WHEN B.sku_category_id = 21 THEN NAME
END) AS category3 ,
MAX(CASE WHEN B.sku_category_id = 31 THEN NAME
END) AS category4
FROM #product A
INNER JOIN #t_ma_sc_sku_relationb B ON A.id = B.sku_id
INNER JOIN #t_ma_sku_category C ON B.sku_category_id = C.id
GROUP BY A.cod
如果name固定为男子,女子,服装,运动休闲,训练这五个。
IF OBJECT_ID('product') IS NOT NULL DROP TABLE product
GO
IF OBJECT_ID('t_ma_sc_sku_relationb') IS NOT NULL DROP TABLE t_ma_sc_sku_relationb
GO
IF OBJECT_ID('t_ma_sku_category') IS NOT NULL DROP TABLE t_ma_sku_category
GO
CREATE TABLE product
(
id INT ,
cod VARCHAR(20)
)
CREATE TABLE t_ma_sc_sku_relationb
(
sku_id INT ,
sku_category_id int
)
CREATE TABLE t_ma_sku_category
(
id INT,
name NVARCHAR(10)
)
INSERT INTO product
SELECT 1,'BB0449-026'
INSERT INTO t_ma_sc_sku_relationb
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 1,11 UNION ALL
SELECT 1,21 UNION ALL
SELECT 1,31
INSERT INTO t_ma_sku_category
SELECT 1,'男子' UNION ALL
SELECT 2,'女子' UNION ALL
SELECT 11,'服装' UNION ALL
SELECT 21,'运动休闲' UNION ALL
SELECT 31,'训练'
SELECT A.cod
,MAX(CASE WHEN B.sku_category_id=1 OR B.sku_category_id=2 THEN '无分区' END) AS category1
,MAX(CASE WHEN B.sku_category_id=11 THEN NAME END) AS category2
,MAX(CASE WHEN B.sku_category_id=21 THEN NAME END) AS category3
,MAX(CASE WHEN B.sku_category_id=31 THEN NAME END) AS category4
FROM product A JOIN t_ma_sc_sku_relationb B ON A.id=B.sku_id JOIN t_ma_sku_category C ON B.sku_category_id=C.id
GROUP BY B.sku_id,A.cod
---------------------------
cod category1 category2 category3 category4
BB0449-026 无分区 服装 运动休闲 训练
create table product(id int, cod varchar(10))
insert into product values(1 , 'BB0449-026')
create table t_ma_sc_sku_relationb(sku_id int,sku_category_id int)
insert into t_ma_sc_sku_relationb values(1 , 1)
insert into t_ma_sc_sku_relationb values(1 , 2)
insert into t_ma_sc_sku_relationb values(1 , 11)
insert into t_ma_sc_sku_relationb values(1 , 21)
insert into t_ma_sc_sku_relationb values(1 , 31)
create table t_ma_sku_category(id int,name varchar(10))
insert into t_ma_sku_category values(1 , '男子')
insert into t_ma_sku_category values(2 , '女子')
insert into t_ma_sku_category values(11, '服装')
insert into t_ma_sku_category values(21, '运动休闲')
insert into t_ma_sku_category values(31, '训练')
go
--如果name固定为男子,女子,服装,运动休闲,训练这五个。
select m.cod,
max(case when name in ('男子','女子') then '无区分' else '' end) category1,
max(case when name = '服装' then '服装' else '' end) category2,
max(case when name = '运动休闲' then '运动休闲' else '' end) category3,
max(case when name = '训练' then '训练' else '' end) category4
from product m,t_ma_sc_sku_relationb n,t_ma_sku_category t
where m.id = n.sku_id and n.sku_category_id = t.id
group by m.cod
drop table product,t_ma_sc_sku_relationb,t_ma_sku_category
/*
cod category1 category2 category3 category4
---------- --------- --------- --------- ---------
BB0449-026 无区分 服装 运动休闲 训练
(所影响的行数为 1 行)
*/