-- 如何查询得出B表中PORT相同但Phone不同的结果集,并显示出A.Name+B.Port、 B.Phone 、C.Phone、C.address如果看题意就是如下:不过后面结实,我也没看清楚.SELECT TA.DK,TA.Phone,TB.Phone,TB.address FROM ( SELECT DK=TA.Name+'-'++TB.Port , TB.Phone FROM A表 TA, ( SELECT * FROM B表 TA WHERE EXISTS (SELECT 1 FROM B表 WHERE TA.Port=Port AND ISNULL(TA.Phone,0)<>ISNULL(Phone,0)) )TB WHERE TA.ID=TB.A_ID ) TA, C表 TB WHERE TA.DK=TB.Name_Port
其实此问题的意思就是A表与B表先联接得出一个结果再与C表比较。
--A表和B表相连接的结果如下: DECLARE @A表 TABLE(ID INT ,NAME VARCHAR(10)) INSERT INTO @A表 SELECT 1,'aaa' UNION ALL SELECT 2,'bbb' UNION ALL SELECT 3,'ccc' DECLARE @B表 TABLE(id INT, A_ID INT, Port VARCHAR(10), Phone VARCHAR(10)) INSERT INTO @B表 SELECT 1,1,'00-01',123 UNION ALL SELECT 2,1,'00-02',456 UNION ALL SELECT 3,1,'00-03',NULL UNION ALL SELECT 4,1,'00-04',157 UNION ALL SELECT 5,2,'00-01',1454 UNION ALL SELECT 6,2,'00-02',NULLSELECT DK=TA.Name+'-'++TB.Port , TB.Phone FROM @A表 TA, ( SELECT * FROM @B表 TA WHERE EXISTS (SELECT 1 FROM @B表 WHERE TA.Port=Port AND ISNULL(TA.Phone,0)<>ISNULL(Phone,0)) )TB WHERE TA.ID=TB.A_ID --满足你的: 如何查询得出B表中PORT相同但Phone不同的结果集 /* DK Phone --------------------- ---------- aaa-00-01 123 aaa-00-02 456 bbb-00-01 1454 bbb-00-02 NULL(4 行受影响) */
--然后把这个结果集与C表连接后结果集合如下:DECLARE @A表 TABLE(ID INT ,NAME VARCHAR(10)) INSERT INTO @A表 SELECT 1,'aaa' UNION ALL SELECT 2,'bbb' UNION ALL SELECT 3,'ccc' DECLARE @B表 TABLE(id INT, A_ID INT, Port VARCHAR(10), Phone VARCHAR(10)) INSERT INTO @B表 SELECT 1,1,'00-01',123 UNION ALL SELECT 2,1,'00-02',456 UNION ALL SELECT 3,1,'00-03',NULL UNION ALL SELECT 4,1,'00-04',157 UNION ALL SELECT 5,2,'00-01',1454 UNION ALL SELECT 6,2,'00-02',NULLDECLARE @C表 TABLE(ID INT, Name_Port VARCHAR(10), Phone VARCHAR(10), address VARCHAR(20)) INSERT INTO @C表 SELECT 1,'aaa-00-01','123','asdf' UNION ALL SELECT 2,'aaa-00-02','','ererer' UNION ALL SELECT 3,'aaa-00-03','1212145454','asdfasdf' UNION ALL SELECT 4,'bbb-00-01','789','sadfasdfasdf' SELECT TA.DK,TA.Phone,TB.Phone,TB.address FROM ( SELECT DK=TA.Name+'-'++TB.Port , TB.Phone FROM @A表 TA, ( SELECT * FROM @B表 TA WHERE EXISTS (SELECT 1 FROM @B表 WHERE TA.Port=Port AND ISNULL(TA.Phone,0)<>ISNULL(Phone,0)) )TB WHERE TA.ID=TB.A_ID ) TA, @C表 TB WHERE TA.DK=TB.Name_Port/* DK Phone Phone address --------------------- ---------- ---------- -------------------- aaa-00-01 123 123 asdf aaa-00-02 456 ererer bbb-00-01 1454 789 sadfasdfasdf(3 行受影响)*/--和你的结果集,不知道为什么差距这么大??
--然后把这个结果集与C表连接后结果集合如下:DECLARE @A表 TABLE(ID INT ,NAME VARCHAR(10)) INSERT INTO @A表 SELECT 1,'aaa' UNION ALL SELECT 2,'bbb' UNION ALL SELECT 3,'ccc' DECLARE @B表 TABLE(id INT, A_ID INT, Port VARCHAR(10), Phone VARCHAR(10)) INSERT INTO @B表 SELECT 1,1,'00-01',123 UNION ALL SELECT 2,1,'00-02',456 UNION ALL SELECT 3,1,'00-03',NULL UNION ALL SELECT 4,1,'00-04',157 UNION ALL SELECT 5,2,'00-01',1454 UNION ALL SELECT 6,2,'00-02',NULLDECLARE @C表 TABLE(ID INT, Name_Port VARCHAR(10), Phone VARCHAR(10), address VARCHAR(20)) INSERT INTO @C表 SELECT 1,'aaa-00-01','123','asdf' UNION ALL SELECT 2,'aaa-00-02','','ererer' UNION ALL SELECT 3,'aaa-00-03','1212145454','asdfasdf' UNION ALL SELECT 4,'bbb-00-01','789','sadfasdfasdf' SELECT TA.DK,TA.Phone,TB.Phone,TB.address FROM ( SELECT DK=TA.Name+'-'++TB.Port , TB.Phone FROM @A表 TA, ( SELECT * FROM @B表 TA WHERE EXISTS (SELECT 1 FROM @B表 WHERE TA.Port=Port AND ISNULL(TA.Phone,0)<>ISNULL(Phone,0)) )TB WHERE TA.ID=TB.A_ID ) TA, @C表 TB WHERE TA.DK=TB.Name_Port/* DK Phone Phone address --------------------- ---------- ---------- -------------------- aaa-00-01 123 123 asdf aaa-00-02 456 ererer bbb-00-01 1454 789 sadfasdfasdf(3 行受影响)*/--和你的结果集,不知道为什么差距这么大??
declare @a table(id int, name varchar(100)) insert @a select '1', 'aaa' union all select '2', 'bbb' union all select '3', 'ccc' declare @b table(id int, A_ID int, Port varchar(100), Phone varchar(100)) insert @b select '1', '1', '00-01', '123' union all select '2', '1', '00-02', '456' union all select '3', '1', '00-03', null union all select '4', '1', '00-04', '157' union all select '5', '2', '00-01', '1454' union all select '6', '2', '00-02', null declare @c table(ID int, Name_Port varchar(100), Phone varchar(100), address varchar(100)) insert @c select '1', 'aaa-00-01', '123', 'asdf' union all select '2', 'aaa-00-02', 'ererer', null union all select '3', 'aaa-00-03', '1212145454', 'asdfasdf' union all select '4', 'bbb-00-01', '789', 'sadfasdfasdf'select DK = a.name + '-' + b.port, [b.Phone] = b.Phone, [c.Phone] = c.Phone, [c.Address] = c.Address from @a a join @b b on a.id = b.a_id left join @c c on a.name + '-' + b.port = c.Name_Port where isnull(b.phone, '') <> isnull(c.phone, '')/* DK B.Phone C.Phone C.address --------------------------- aaa-00-02 456 ererer NULL aaa-00-03 NULL 1212145454 asdfasdf aaa-00-04 157 NULL NULL bbb-00-01 1454 789 sadfasdfasdf */
我试了下Limpire(昨夜小楼)大哥的,结果是对的。我现在在加上一列,但是用通配符进行匹配就出不来了,大家看下如何解决。declare @a table(id int, name varchar(100)) insert @a select '1', 'aaa' union all select '2', 'bbb' union all select '3', 'ccc' declare @b table(id int, A_ID int, Port varchar(100), Phone varchar(100)) insert @b select '1', '1', '00-01', '123' union all select '2', '1', '00-02', '456' union all select '3', '1', '00-03', null union all select '4', '1', '00-04', '157' union all select '5', '2', '00-01', '1454' union all select '6', '2', '00-02', null declare @c table(ID int, Name_Port varchar(100), Phone varchar(100), address varchar(100),phone2 varchar(100)) insert @c select '1', 'aaa-00-01', '123', 'asdf','123@aaa' union all select '2', 'aaa-00-02', 'ererer', null, 'ererer@aaa' union all select '3', 'aaa-00-03', '1212145454', 'asdfasdf','1212145454@aaa' union all select '4', 'bbb-00-01', '789', 'sadfasdfasdf','789@bbb' select DK = a.name + '-' + b.port, [b.Phone] = b.Phone, [c.Phone] = c.Phone, [c.Address] = c.Address from @a a join @b b on a.id = b.a_id left join @c c on a.name + '-' + b.port = c.Name_Port where isnull(b.phone, '') <> isnull(c.phone, '') and isnull(c.phone2,'') not like '%'+isnull(b.phone,'')+'%' 想要得出 DK B.Phone C.Phone C.address --------------------------- aaa-00-02 456 ererer NULL aaa-00-03 NULL 1212145454 asdfasdf aaa-00-04 157 NULL NULL bbb-00-01 1454 789 sadfasdfasdf 可实际只有3条记录。 aaa-00-02 456 ererer NULL aaa-00-04 157 NULL NULL bbb-00-01 1454 789 sadfasdfasdf 其中少了一条aaa-00-03 NULL 1212145454 asdfasdf的记录。 如何解决。
declare @a table(id int, name varchar(100)) insert @a select '1', 'aaa' union all select '2', 'bbb' union all select '3', 'ccc' declare @b table(id int, A_ID int, Port varchar(100), Phone varchar(100)) insert @b select '1', '1', '00-01', '123' union all select '2', '1', '00-02', '456' union all select '3', '1', '00-03', null union all select '4', '1', '00-04', '157' union all select '5', '2', '00-01', '1454' union all select '6', '2', '00-02', null declare @c table(ID int, Name_Port varchar(100), Phone varchar(100), address varchar(100),phone2 varchar(100)) insert @c select '1', 'aaa-00-01', '123', 'asdf','123@aaa' union all select '2', 'aaa-00-02', 'ererer', null, 'ererer@aaa' union all select '3', 'aaa-00-03', '1212145454', 'asdfasdf','1212145454@aaa' union all select '4', 'bbb-00-01', '789', 'sadfasdfasdf','789@bbb' select DK = a.name + '-' + b.port, [b.Phone] = b.Phone, [c.Phone] = c.Phone, [c.Address] = c.Address from @a a join @b b on a.id = b.a_id left join @c c on a.name + '-' + b.port = c.Name_Port where isnull(b.phone, '') <> isnull(c.phone, '') and charindex(isnull(b.Phone, ''), isnull(c.Phone2, '')) = 0
其中少了一条aaa-00-03 NULL 1212145454 asdfasdf的记录。 ----------------------------------------------c.Phone = null b.Phone2 = '1212145454@aaa'isnull(c.Phone2,'') not like '%'+isnull(b.Phone,'')+'%'代入测试一下:if '1212145454@aaa' not like '%%' print 'not like' else print 'like'
Limpire(昨夜小楼)解决了,谢谢你的帮助。我一直在找原因,原来是isnull(c.phone2,'') not like '%'+isnull(b.phone,'')+'%'这句错了。charindex(isnull(b.Phone, ''), isnull(c.Phone2, '')) = 0这样才是正确的。我马上结帖。再次感谢大家!
暂不考虑A表,B表就两条01-01记录,一条Phone=123,一条1454,何来相等?
(
SELECT DK=TA.Name+'-'++TB.Port ,
TB.Phone
FROM A表 TA,
(
SELECT * FROM B表 TA WHERE EXISTS (SELECT 1 FROM B表 WHERE TA.Port=Port AND ISNULL(TA.Phone,0)<>ISNULL(Phone,0))
)TB
WHERE TA.ID=TB.A_ID
) TA,
C表 TB
WHERE TA.DK=TB.Name_Port
DECLARE @A表 TABLE(ID INT ,NAME VARCHAR(10))
INSERT INTO @A表
SELECT 1,'aaa'
UNION ALL
SELECT 2,'bbb'
UNION ALL
SELECT 3,'ccc'
DECLARE @B表 TABLE(id INT, A_ID INT, Port VARCHAR(10), Phone VARCHAR(10))
INSERT INTO @B表
SELECT 1,1,'00-01',123
UNION ALL
SELECT 2,1,'00-02',456
UNION ALL
SELECT 3,1,'00-03',NULL
UNION ALL
SELECT 4,1,'00-04',157
UNION ALL
SELECT 5,2,'00-01',1454
UNION ALL
SELECT 6,2,'00-02',NULLSELECT DK=TA.Name+'-'++TB.Port ,
TB.Phone
FROM @A表 TA,
(
SELECT * FROM @B表 TA WHERE EXISTS (SELECT 1 FROM @B表 WHERE TA.Port=Port AND ISNULL(TA.Phone,0)<>ISNULL(Phone,0))
)TB
WHERE TA.ID=TB.A_ID
--满足你的: 如何查询得出B表中PORT相同但Phone不同的结果集
/*
DK Phone
--------------------- ----------
aaa-00-01 123
aaa-00-02 456
bbb-00-01 1454
bbb-00-02 NULL(4 行受影响)
*/
INSERT INTO @A表
SELECT 1,'aaa'
UNION ALL
SELECT 2,'bbb'
UNION ALL
SELECT 3,'ccc'
DECLARE @B表 TABLE(id INT, A_ID INT, Port VARCHAR(10), Phone VARCHAR(10))
INSERT INTO @B表
SELECT 1,1,'00-01',123
UNION ALL
SELECT 2,1,'00-02',456
UNION ALL
SELECT 3,1,'00-03',NULL
UNION ALL
SELECT 4,1,'00-04',157
UNION ALL
SELECT 5,2,'00-01',1454
UNION ALL
SELECT 6,2,'00-02',NULLDECLARE @C表 TABLE(ID INT, Name_Port VARCHAR(10), Phone VARCHAR(10), address VARCHAR(20))
INSERT INTO @C表
SELECT 1,'aaa-00-01','123','asdf'
UNION ALL
SELECT 2,'aaa-00-02','','ererer'
UNION ALL
SELECT 3,'aaa-00-03','1212145454','asdfasdf'
UNION ALL
SELECT 4,'bbb-00-01','789','sadfasdfasdf'
SELECT TA.DK,TA.Phone,TB.Phone,TB.address FROM
(
SELECT DK=TA.Name+'-'++TB.Port ,
TB.Phone
FROM @A表 TA,
(
SELECT * FROM @B表 TA WHERE EXISTS (SELECT 1 FROM @B表 WHERE TA.Port=Port AND ISNULL(TA.Phone,0)<>ISNULL(Phone,0))
)TB
WHERE TA.ID=TB.A_ID
) TA,
@C表 TB
WHERE TA.DK=TB.Name_Port/*
DK Phone Phone address
--------------------- ---------- ---------- --------------------
aaa-00-01 123 123 asdf
aaa-00-02 456 ererer
bbb-00-01 1454 789 sadfasdfasdf(3 行受影响)*/--和你的结果集,不知道为什么差距这么大??
INSERT INTO @A表
SELECT 1,'aaa'
UNION ALL
SELECT 2,'bbb'
UNION ALL
SELECT 3,'ccc'
DECLARE @B表 TABLE(id INT, A_ID INT, Port VARCHAR(10), Phone VARCHAR(10))
INSERT INTO @B表
SELECT 1,1,'00-01',123
UNION ALL
SELECT 2,1,'00-02',456
UNION ALL
SELECT 3,1,'00-03',NULL
UNION ALL
SELECT 4,1,'00-04',157
UNION ALL
SELECT 5,2,'00-01',1454
UNION ALL
SELECT 6,2,'00-02',NULLDECLARE @C表 TABLE(ID INT, Name_Port VARCHAR(10), Phone VARCHAR(10), address VARCHAR(20))
INSERT INTO @C表
SELECT 1,'aaa-00-01','123','asdf'
UNION ALL
SELECT 2,'aaa-00-02','','ererer'
UNION ALL
SELECT 3,'aaa-00-03','1212145454','asdfasdf'
UNION ALL
SELECT 4,'bbb-00-01','789','sadfasdfasdf'
SELECT TA.DK,TA.Phone,TB.Phone,TB.address FROM
(
SELECT DK=TA.Name+'-'++TB.Port ,
TB.Phone
FROM @A表 TA,
(
SELECT * FROM @B表 TA WHERE EXISTS (SELECT 1 FROM @B表 WHERE TA.Port=Port AND ISNULL(TA.Phone,0)<>ISNULL(Phone,0))
)TB
WHERE TA.ID=TB.A_ID
) TA,
@C表 TB
WHERE TA.DK=TB.Name_Port/*
DK Phone Phone address
--------------------- ---------- ---------- --------------------
aaa-00-01 123 123 asdf
aaa-00-02 456 ererer
bbb-00-01 1454 789 sadfasdfasdf(3 行受影响)*/--和你的结果集,不知道为什么差距这么大??
insert @a
select '1', 'aaa' union all
select '2', 'bbb' union all
select '3', 'ccc'
declare @b table(id int, A_ID int, Port varchar(100), Phone varchar(100))
insert @b
select '1', '1', '00-01', '123' union all
select '2', '1', '00-02', '456' union all
select '3', '1', '00-03', null union all
select '4', '1', '00-04', '157' union all
select '5', '2', '00-01', '1454' union all
select '6', '2', '00-02', null
declare @c table(ID int, Name_Port varchar(100), Phone varchar(100), address varchar(100))
insert @c
select '1', 'aaa-00-01', '123', 'asdf' union all
select '2', 'aaa-00-02', 'ererer', null union all
select '3', 'aaa-00-03', '1212145454', 'asdfasdf' union all
select '4', 'bbb-00-01', '789', 'sadfasdfasdf'select DK = a.name + '-' + b.port, [b.Phone] = b.Phone, [c.Phone] = c.Phone, [c.Address] = c.Address from @a a join @b b on a.id = b.a_id left join @c c on a.name + '-' + b.port = c.Name_Port where isnull(b.phone, '') <> isnull(c.phone, '')/*
DK B.Phone C.Phone C.address
---------------------------
aaa-00-02 456 ererer NULL
aaa-00-03 NULL 1212145454 asdfasdf
aaa-00-04 157 NULL NULL
bbb-00-01 1454 789 sadfasdfasdf
*/
a.Name + '-' + b.Port = c.Name_Port
但是:
b.Phone <> c.Phone是这样吧?表述清楚了,早解决了。
a.Name + '-' + b.Port = c.Name_Port
但是:
b.Phone <> c.Phone是这样吧?表述清楚了,早解决了。
Limpire(昨夜小楼)大哥说的对。我忘了写'-'了,不好意思.
insert @a
select '1', 'aaa' union all
select '2', 'bbb' union all
select '3', 'ccc'
declare @b table(id int, A_ID int, Port varchar(100), Phone varchar(100))
insert @b
select '1', '1', '00-01', '123' union all
select '2', '1', '00-02', '456' union all
select '3', '1', '00-03', null union all
select '4', '1', '00-04', '157' union all
select '5', '2', '00-01', '1454' union all
select '6', '2', '00-02', null
declare @c table(ID int, Name_Port varchar(100), Phone varchar(100), address varchar(100),phone2 varchar(100))
insert @c
select '1', 'aaa-00-01', '123', 'asdf','123@aaa' union all
select '2', 'aaa-00-02', 'ererer', null, 'ererer@aaa' union all
select '3', 'aaa-00-03', '1212145454', 'asdfasdf','1212145454@aaa' union all
select '4', 'bbb-00-01', '789', 'sadfasdfasdf','789@bbb'
select DK = a.name + '-' + b.port, [b.Phone] = b.Phone, [c.Phone] = c.Phone, [c.Address] = c.Address
from @a a join @b b on a.id = b.a_id left join @c c on a.name + '-' + b.port = c.Name_Port
where isnull(b.phone, '') <> isnull(c.phone, '') and isnull(c.phone2,'') not like '%'+isnull(b.phone,'')+'%'
想要得出
DK B.Phone C.Phone C.address
---------------------------
aaa-00-02 456 ererer NULL
aaa-00-03 NULL 1212145454 asdfasdf
aaa-00-04 157 NULL NULL
bbb-00-01 1454 789 sadfasdfasdf
可实际只有3条记录。
aaa-00-02 456 ererer NULL
aaa-00-04 157 NULL NULL
bbb-00-01 1454 789 sadfasdfasdf
其中少了一条aaa-00-03 NULL 1212145454 asdfasdf的记录。
如何解决。
insert @a
select '1', 'aaa' union all
select '2', 'bbb' union all
select '3', 'ccc'
declare @b table(id int, A_ID int, Port varchar(100), Phone varchar(100))
insert @b
select '1', '1', '00-01', '123' union all
select '2', '1', '00-02', '456' union all
select '3', '1', '00-03', null union all
select '4', '1', '00-04', '157' union all
select '5', '2', '00-01', '1454' union all
select '6', '2', '00-02', null
declare @c table(ID int, Name_Port varchar(100), Phone varchar(100), address varchar(100),phone2 varchar(100))
insert @c
select '1', 'aaa-00-01', '123', 'asdf','123@aaa' union all
select '2', 'aaa-00-02', 'ererer', null, 'ererer@aaa' union all
select '3', 'aaa-00-03', '1212145454', 'asdfasdf','1212145454@aaa' union all
select '4', 'bbb-00-01', '789', 'sadfasdfasdf','789@bbb'
select DK = a.name + '-' + b.port, [b.Phone] = b.Phone, [c.Phone] = c.Phone, [c.Address] = c.Address
from @a a join @b b on a.id = b.a_id left join @c c on a.name + '-' + b.port = c.Name_Port
where isnull(b.phone, '') <> isnull(c.phone, '') and charindex(isnull(b.Phone, ''), isnull(c.Phone2, '')) = 0
----------------------------------------------c.Phone = null
b.Phone2 = '1212145454@aaa'isnull(c.Phone2,'') not like '%'+isnull(b.Phone,'')+'%'代入测试一下:if '1212145454@aaa' not like '%%' print 'not like'
else print 'like'