-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-13 11:20:30
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
Go
CREATE TABLE tb1(名称 NVARCHAR(1),数量 INT)
Go
INSERT INTO tb1
SELECT 'R',1 UNION ALL
SELECT 'R',2 UNION ALL
SELECT 'R',4 UNION ALL
SELECT 'R',6
GOSELECT * FROM TB1
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-13 11:21:02
IF OBJECT_ID('tb2') IS NOT NULL
DROP TABLE tb2
Go
CREATE TABLE tb2(名称 NVARCHAR(1),数量 NVARCHAR(1),金额 INT)
Go
INSERT INTO tb2
SELECT 'R','0',2 UNION ALL
SELECT 'R','0',4 UNION ALL
SELECT 'R','1',5 UNION ALL
SELECT 'R','5',3 UNION ALL
SELECT 'R','8',6
GOSELECT * FROM TB2
SELECT * FROM TB1
with wang as
(
select a.数量,b.金额 ,差=a.数量-b.数量 from tb1 a,tb2 b
where a.名称=b.名称 and b.数量<a.数量
)select 数量,金额 from wang t where not exists(select 1 from wang where 数量=t.数量 and 差<t.差)数量 金额 差
----------- ----------- -----------
1 2 1
1 4 1
2 5 1
4 5 3
6 3 1(5 行受影响)
麻烦一点也好 能不能在指教下
select tb1.name,tb1.qty as tb1_qty,tb2.qty as tb2_qty,tb2.value
from tb1
left join tb2
on tb1.name=tb2.name
and tb1.qty>tb2.qty
and not exists(select 1 from tb2 a where a.name=tb2.name and a.qty>tb2.qty and a.qty<tb1.qty)/*
name tb1_qty tb2_qty value
---- ----------- ------- -----------
R 1 0 2
R 1 0 4
R 2 1 5
R 4 1 5
R 6 5 3
*/
但是若不显示name,b表的数量 就不可以显示要求的结果表哦