SQL記錄篩選　語句怎樣寫

2007-02-08 06:43:29 303 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

VMware ESXi 6.7 U3v 补丁升级实战：从 ISO 到 ZIP 包的 3 种安全更新路径本文详细介绍了VMware ESXi 6.7 U3v补丁升级的三种高效方案，包括vSphere Client图形界面升级、ESXCLI命令行升级和离线Bundle升级。针对系统管理员和运维工程师的实际需求，提供了从前期准备到后期验证的完整流程，确保升级过程的安全性和稳定性。特别强调了完整性校验和硬件兼容性检查的重要性，帮助用户避免常见升级问题。阅读详情

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'的記錄?

...全文

VMware ESXi 8.0U3j 官方下载指南：Broadcom支持门户3步获取ISO与补丁本文详细介绍了如何在Broadcom支持门户中获取VMware ESXi 8.0U3j官方ISO与补丁，包括账户准备、文件下载验证及安装部署全流程。针对Broadcom收购后的新变化，提供了版本选择建议、实战安装步骤和性能优化技巧，帮助系统管理员高效搭建虚拟化环境。阅读详情

sunygd

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

sunylf 2007-02-09

打赏
举报

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

marco08 2007-02-08

打赏
举报

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)

sunygd 2007-02-08

打赏
举报

基於以上表的記錄，
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中沒有的品號的記錄來

zhfpoet 2007-02-08

打赏
举报

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

sunygd 2007-02-08

打赏
举报

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'

有沒有更優解法？

sunygd 2007-02-08

打赏
举报

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 2007-02-08

打赏
举报

樓上的不對啊。我要的是一條記錄啊，你的結果是
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 一條記錄

Andy-W 2007-02-08

打赏
举报

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

sunygd 2007-02-08

打赏
举报

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

SQL記錄篩選 語句怎樣寫

相关推荐

SQL記錄篩選　語句怎樣寫