--1. select * from 订单表 a where not exists(select 1 from 评论表 b where a.ordeID=b.ordeID and a.proid=b.proid)--2. select * from 订单表 except select * from 评论表
标准答案都被1楼说完了. SELECT C.ordeID, C.proid FROM ( SELECT * FROM 订单表 UNION ALL SELECT * FROM 评论表 ) C GROUP BY C.ordeID, C.proid HAVING COUNT(*) < 2 ORDER BY C.ordeID, C.proid
select * from 订单表 a where proid not in (select proid from 评论表 where ordeID=a.ordeID)
谢谢各位了,特别鸣谢 Dlut_LIuQ 呵呵,不过这里还有个问题没有说 就是两个表订单表 ordeID proid1 120 1 119 2 120 S码 红色 2 120 M码 红色 2 120 S码 绿色 2 110 评论表orderID proid 1 120 这个时候 select * from 订单表 a where not exists(select 1 from 评论表 b where a.ordeID=b.ordeID and a.proid=b.proid)这个会取出所有,可是 相同订单号,产品ID 也相同的记录我现在只需要一条。不需要全部 谢谢哦
select distinct ordeID,proid from 订单表 a where not exists(select 1 from 评论表 b where a.ordeID=b.ordeID and a.proid=b.proid)
select * from tb1 where not exists (select 1 from tb2 where tb1.orderid=orderid and table1.proid=proid)
declare @订单表 table (ordeID int,proid int,c3 varchar(3),c4 varchar(4)) insert into @订单表 select 1,120,null,null union all select 1,119,null,null union all select 2,120,'S码','红色' union all select 2,120,'M码','红色' union all select 2,120,'S码','绿色' union all select 2,110,null,nulldeclare @评论表 table (orderID int,proid int) insert into @评论表 select 1,120SELECT ordeID ,proid,MAX(c3),MAX(c4) FROM ( SELECT * FROM @订单表 a WHERE NOT EXISTS ( SELECT 1 FROM @评论表 b WHERE a.ordeID = b.orderID AND a.proid = b.proid ) )c GROUP BY ordeID ,proid /* ordeID proid ----------- ----------- ---- ---- 2 110 NULL NULL 1 119 NULL NULL 2 120 S码 绿色 */
数据量大么? 如果数据量大的话not in的速度会比较慢。
select min(ordeID),proid from 订单表 a where not exists(select 1 from 评论表 b where a.ordeID=b.ordeID and a.proid=b.proid) group by ordeID
declare @订单表 table (orderID int,proid int,c3 varchar(3),c4 varchar(4)) insert into @订单表 select 1,120,null,null union all select 1,119,null,null union all select 2,120,'S码','红色' union all select 2,120,'M码','红色' union all select 2,120,'S码','绿色' union all select 2,110,null,nulldeclare @评论表 table (orderID int,proid int) insert into @评论表 select 1,120select b.* from (select distinct orderID, Proid from @订单表) a cross apply (select top(1) * from @订单表 c where a.orderID=c.orderID and a.proid=c.proid) b where not exists(select top 1 1 from @评论表 b where a.orderID=b.orderID and a.proid=b.proid)
select * from 订单表 a
where not exists(select 1 from 评论表 b where a.ordeID=b.ordeID and a.proid=b.proid)--2.
select * from 订单表
except
select * from 评论表
SELECT C.ordeID, C.proid FROM
(
SELECT * FROM 订单表
UNION ALL
SELECT * FROM 评论表
) C
GROUP BY C.ordeID, C.proid
HAVING COUNT(*) < 2
ORDER BY C.ordeID, C.proid
呵呵,不过这里还有个问题没有说
就是两个表订单表
ordeID proid1 120
1 119
2 120 S码 红色
2 120 M码 红色
2 120 S码 绿色
2 110
评论表orderID proid
1 120
这个时候
select * from 订单表 a
where not exists(select 1 from 评论表 b where a.ordeID=b.ordeID and a.proid=b.proid)这个会取出所有,可是 相同订单号,产品ID 也相同的记录我现在只需要一条。不需要全部
谢谢哦
from 订单表 a
where not exists(select 1 from 评论表 b where a.ordeID=b.ordeID and a.proid=b.proid)
from tb1
where not exists (select 1 from tb2 where tb1.orderid=orderid and table1.proid=proid)
declare @订单表 table (ordeID int,proid int,c3 varchar(3),c4 varchar(4))
insert into @订单表
select 1,120,null,null union all
select 1,119,null,null union all
select 2,120,'S码','红色' union all
select 2,120,'M码','红色' union all
select 2,120,'S码','绿色' union all
select 2,110,null,nulldeclare @评论表 table (orderID int,proid int)
insert into @评论表
select 1,120SELECT ordeID ,proid,MAX(c3),MAX(c4) FROM (
SELECT *
FROM @订单表 a
WHERE NOT EXISTS ( SELECT 1
FROM @评论表 b
WHERE a.ordeID = b.orderID
AND a.proid = b.proid )
)c GROUP BY ordeID ,proid
/*
ordeID proid
----------- ----------- ---- ----
2 110 NULL NULL
1 119 NULL NULL
2 120 S码 绿色
*/
如果数据量大的话not in的速度会比较慢。
select min(ordeID),proid
from 订单表 a
where not exists(select 1 from 评论表 b where a.ordeID=b.ordeID and a.proid=b.proid)
group by ordeID
insert into @订单表
select 1,120,null,null union all
select 1,119,null,null union all
select 2,120,'S码','红色' union all
select 2,120,'M码','红色' union all
select 2,120,'S码','绿色' union all
select 2,110,null,nulldeclare @评论表 table (orderID int,proid int)
insert into @评论表
select 1,120select b.* from
(select distinct orderID, Proid from @订单表) a
cross apply
(select top(1) * from @订单表 c where a.orderID=c.orderID and a.proid=c.proid) b
where not exists(select top 1 1 from @评论表 b where a.orderID=b.orderID and a.proid=b.proid)