------------------------------------------------------------------------ -- Author: happyflystone -- Date : 2009-01-07 11:01:38 -- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) -- Apr 14 2006 01:12:25 -- Copyright (c) 1988-2005 Microsoft Corporation -- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) -- -------------------------------------------------------------------------- Test Data: add_final IF OBJECT_ID('add_final') IS NOT NULL DROP TABLE add_final Go CREATE TABLE add_final(F6 NVARCHAR(7),F7 INT,F27 NVARCHAR(7)) Go INSERT INTO add_final SELECT '方里镇野狐咀村',174,'D130503' UNION ALL SELECT '方里镇陶家庄村',295,'D130501' GO -- Test Data: planadmin IF OBJECT_ID('planadmin') IS NOT NULL DROP TABLE planadmin Go CREATE TABLE planadmin(pro_name NVARCHAR(10),re NVARCHAR(31)) Go INSERT INTO planadmin SELECT '华子山供水工程','D130502、D130504、D130501、D130524' UNION ALL SELECT '陶家庄供水工程','D130501' UNION ALL SELECT '方里镇野狐咀供水工程','D130501' UNION ALL SELECT '方里镇华子山供水工程','D130501' UNION ALL SELECT '曹家庄村供水工程','D130501' GO --Start select a.*, case when exists(select 1 from planadmin where charindex('、'+a.f27+'、','、'+re+',') > 0) then 1 else 0 end as keyid from add_final as a--Result: /* F6 F7 F27 keyid ------- ----------- ------- ----------- 方里镇野狐咀村 174 D130503 0 方里镇陶家庄村 295 D130501 1(2 行受影响) */ --End
1楼的写的是: select a.*,case when exists(select 1 from planadmin where charindex('、'+a.f27+'、','、'+re+',') > 0 then 1 else 0 end) as keyid from add_final as a4楼写的是: select a.*, case when exists(select 1 from planadmin where charindex('、'+a.f27+'、','、'+re+',') > 0) then 1 else 0 end as keyid from add_final as a 这两个不同的语句有本质的区别吗??
------------------------------------------------------------------------
-- Author: happyflystone
-- Date : 2009-01-07 11:01:38
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: add_final
IF OBJECT_ID('add_final') IS NOT NULL
DROP TABLE add_final
Go
CREATE TABLE add_final(F6 NVARCHAR(7),F7 INT,F27 NVARCHAR(7))
Go
INSERT INTO add_final
SELECT '方里镇野狐咀村',174,'D130503' UNION ALL
SELECT '方里镇陶家庄村',295,'D130501'
GO
-- Test Data: planadmin
IF OBJECT_ID('planadmin') IS NOT NULL
DROP TABLE planadmin
Go
CREATE TABLE planadmin(pro_name NVARCHAR(10),re NVARCHAR(31))
Go
INSERT INTO planadmin
SELECT '华子山供水工程','D130502、D130504、D130501、D130524' UNION ALL
SELECT '陶家庄供水工程','D130501' UNION ALL
SELECT '方里镇野狐咀供水工程','D130501' UNION ALL
SELECT '方里镇华子山供水工程','D130501' UNION ALL
SELECT '曹家庄村供水工程','D130501'
GO
--Start
select a.*,
case when exists(select 1 from planadmin where charindex('、'+a.f27+'、','、'+re+',') > 0) then 1 else 0 end as keyid
from add_final as a--Result:
/*
F6 F7 F27 keyid
------- ----------- ------- -----------
方里镇野狐咀村 174 D130503 0
方里镇陶家庄村 295 D130501 1(2 行受影响)
*/
--End
select a.*,case when exists(select 1 from planadmin where charindex('、'+a.f27+'、','、'+re+',') > 0 then 1 else 0 end) as keyid
from add_final as a4楼写的是:
select a.*,
case when exists(select 1 from planadmin where charindex('、'+a.f27+'、','、'+re+',') > 0) then 1 else 0 end as keyid
from add_final as a
这两个不同的语句有本质的区别吗??