噢。A和B表是通过A的ID连接,是一对多的关系。即B中的A_ID是A中的ID。
解决方案 »
- 请高手帮忙
- 第 20 行: 'd' 附近有语法错误。这个应该怎么修正呢
- 如何找出时间有交叉的记录
- 比照一表格数据,更新另一表格数据!(江湖救急~~~抱拳为礼)
- select * from tablea,tableb的写法和inner join的写法可以得到同样的结果,效率上有什么区别吗???
- 在使用企业管理器时提示缺少repbr.dll
- SQL查询
- 两个时间字符类型‘13:30:00’‘11:00:00’,相减得到‘2.5小时’
- 一个vb数据库编程的难题,请教高手!!急急急!!!
- File must be opened exclusively.
- 时间累加问题
- nvarchar类型转换smalldatetime
暂不考虑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'