如有表
BillNo Product
0001 12
0002 11
0002 12
0003 13
0003 12
0004 11现在要查询出订了12的这个产品的订单中的其他商品有哪些
如上查询出来为
11
13谢谢也就是要实现 买了当前商品的客人还买了哪些商品
BillNo Product
0001 12
0002 11
0002 12
0003 13
0003 12
0004 11现在要查询出订了12的这个产品的订单中的其他商品有哪些
如上查询出来为
11
13谢谢也就是要实现 买了当前商品的客人还买了哪些商品
insert @t select '0001' , 12
insert @t select '0002' , 11
insert @t select '0002' , 12
insert @t select '0003' , 13
insert @t select '0003', 12
insert @t select '0004' , 11
select Product from @t where BillNo in(select BillNo from @t where product=12) and product<>12
/*
Product
-----------
11
13
*/
INSERT @t SELECT '0001', 12
UNION ALL SELECT '0002' , 11
UNION ALL SELECT '0002' , 12
UNION ALL SELECT '0003' , 13
UNION ALL SELECT '0003' ,12
UNION ALL SELECT '0004' ,11 SELECT DISTINCT a.Product FROM @t a
FULL JOIN @t b
ON a.billNo=b.billNo
WHERE b.product = 12/*
11
12
13
*/
FROM TAB
WHERE BILLNO IN
(
SELECT BILLNO
FROM TAB
WHERE PRODUCT=12
)
AND PRODUCT<>12
declare @t table(BillNo varchar(8),Product int)
insert into @t values('0001',12)
insert into @t values('0002',11)
insert into @t values('0002',12)
insert into @t values('0003',13)
insert into @t values('0003',12)
insert into @t values('0004',11) select distinct Product from @t t where exists(select 1 from @t where BillNo=t.BillNo and Product=12) and Product!=12/*
Product
-----------
11
13
*/
INSERT @t SELECT '0001', 12
UNION ALL SELECT '0002' , 11
UNION ALL SELECT '0002' , 12
UNION ALL SELECT '0003' , 13
UNION ALL SELECT '0003' ,12
UNION ALL SELECT '0004' ,11 SELECT DISTINCT a.Product FROM @t a
FULL JOIN @t b
ON a.billNo=b.billNo
WHERE b.product = 12 AND a.product !=12/*
11
13
*/
insert TB
select 001,12
union all
select 002,11
union all
select 002,12
union all
select 003,13
union all
select 003,12
union all
select 004,11select Product
from TB
where BillNO in
(
select BillNo
from TB
where Product = 12
)
and Product <>12drop table TB