SQL記錄篩選 語句怎樣寫

[sunygd]

CREATE TABLE T1(
T1DH VARCHAR(10),
PH VARCHAR(20),
SL Numeric(9,4) )

CREATE TABLE T2(
T2DH VARCHAR(10),
PH VARCHAR(20),
SL Numeric(9,4) )

INSERT INTO T1
SELECT 'T1A1','A','10'
UNION ALL
SELECT 'T1A1','B','5'
UNION ALL
SELECT 'T1A2','B','5'
UNION ALL
SELECT 'T1A2','C','5'

INSERT INTO T2
SELECT 'T2A1','A','10'
UNION ALL
SELECT 'T2A1','B','5'
UNION ALL
SELECT 'T2A1','C','5'
UNION ALL
SELECT 'T2A2','B','5'
UNION ALL
SELECT 'T2A2','C','5'

SELECT * FROM T1
SELECT * FROM T2
如何將 T2中的 'T2A1' ,'C','5' 通過一條語句插入到T1中

insert INTO t1(T1DH,PH,SL)
SELECT T1.T1DH,T2.PH,T2.SL
FROM T2,T1
WHERE T2DH='T2A1' AND T1DH='T1A1' AND NOT EXISTS(SELECT * FROM T1,T2 WHERE T1DH='T1A1' AND T2DH='T2A1' AND T1.PH=T2.PH)
使t1中增加一條'T1A1','C','5'的記錄?

[sunygd]

老大們呢？快來幫忙看看啊！！！

[Andy-W]

INSERT INTO T1 SELECT * FROM T2 WHERE NOT EXISTS(SELECT 1 FROM T1 WHERE T1.T1DH=T2.T2DH AND T1.PH=T2.PH)

[sunygd]

樓上的不對啊。我要的是一條記錄啊，你的結果是
T2A1 A 10.0000
T2A1 B 5.0000
T2A1 C 5.0000
T2A1 D 5.0000
T2A2 B 5.0000
T2A2 C 5.0000
我要只是 T2A1 C 5 一條記錄

[sunygd]

CREATE TABLE T1(
T1DH VARCHAR(10),
PH VARCHAR(20),
SL Numeric(9,4) )

CREATE TABLE T2(
T2DH VARCHAR(10),
PH VARCHAR(20),
SL Numeric(9,4) )

INSERT INTO T1
SELECT 'T1A1','A','10'
UNION ALL
SELECT 'T1A1','B','2'
UNION ALL
SELECT 'T1A1','E','4'
UNION ALL
SELECT 'T1A2','B','6'
UNION ALL
SELECT 'T1A2','C','12'

INSERT INTO T2
SELECT 'T2A1','A','10'
UNION ALL
SELECT 'T2A1','B','1'
UNION ALL
SELECT 'T2A1','C','3'
UNION ALL
SELECT 'T2A1','D','5'
UNION ALL
SELECT 'T2A2','B','7'
UNION ALL
SELECT 'T2A2','C','9'

--DELETE FROM T1
--DELETE FROM T2



如何將 T2中的 'T2A1' ,'C','5' 通過一條語句插入到T1中
insert INTO t1(T1DH,PH,SL)

select * from t2 where T2DH='T2A1'

SELECT * FROM T1
SELECT * FROM T2

select 't1a1',ph,sl from (
SELECT * from t1
where t1dh='t1a1'
union
select * from t2
where t2dh='t2a1'
) t
WHERE ph NOT in (SELECT t1.ph FROM T1,T2 WHERE T1DH='T1A1' AND T2DH='T2A1' AND T1.PH=T2.PH)
AND T1DH='T2A1'

有沒有更優解法？

[sunygd]

CREATE TABLE T1(
T1DH VARCHAR(10),
PH VARCHAR(20),
SL Numeric(9,4) )

CREATE TABLE T2(
T2DH VARCHAR(10),
PH VARCHAR(20),
SL Numeric(9,4) )

INSERT INTO T1
SELECT 'T1A1','A','10'
UNION ALL
SELECT 'T1A1','B','2'
UNION ALL
SELECT 'T1A1','E','4'
UNION ALL
SELECT 'T1A2','B','6'
UNION ALL
SELECT 'T1A2','C','12'

INSERT INTO T2
SELECT 'T2A1','A','10'
UNION ALL
SELECT 'T2A1','B','1'
UNION ALL
SELECT 'T2A1','C','3'
UNION ALL
SELECT 'T2A1','D','5'
UNION ALL
SELECT 'T2A2','B','7'
UNION ALL
SELECT 'T2A2','C','9'

--DELETE FROM T1
--DELETE FROM T2



如何將 T2中的 t2dh='T2A1' ,t2.ph 有 t1dh='T1A1',T1.PH沒有的記錄 通過一條語句插入到T1中

SELECT * FROM T1
SELECT * FROM T2

--insert INTO t1(T1DH,PH,SL)

select 't1a1',ph,sl from (
SELECT * from t1
where t1dh='t1a1'
union
select * from t2
where t2dh='t2a1'
) t
WHERE ph NOT in (SELECT t1.ph FROM T1,T2 WHERE T1DH='T1A1' AND T2DH='T2A1' AND T1.PH=T2.PH)
AND T1DH='T2A1'

有沒有更優解法？

[zhfpoet]

你把问题描述清楚一些啥
要不然就这样好拉
insert INTO t1(T1DH,PH,SL)
SELECT 'T1A1','C','5'

[sunygd]

基於以上表的記錄，
select 't1a1',ph,sl from (
SELECT * from t1
where t1dh='t1a1'
union
select * from t2
where t2dh='t2a1'
) t
WHERE ph NOT in (SELECT t1.ph FROM T1,T2 WHERE T1DH='T1A1' AND T2DH='T2A1' AND T1.PH=T2.PH)
AND T1DH='T2A1'

這個語句有沒有更優寫法？
求出t1中單號='T1A1',T2中單號='T2A1'中T2中有的品號，T1中沒有的品號的記錄來

[marco08]

insert T1
select * from T2 as tmp
where T2DH='T2A1' and not exists
(select 1 from T1 where T1DH='T1A1' and PH=tmp.PH)

select * from T1

--result
T1DH PH SL
---------- -------------------- -----------
T1A1 A 10.0000
T1A1 B 5.0000
T1A2 B 5.0000
T1A2 C 5.0000
T2A1 C 5.0000

(5 row(s) affected)

[sunylf]

marco08(天道酬勤)
　感謝你的回復，確實是高手。