表 a
username
张三
李四
王五
表 b filename
张明
李四
赵六然后以表 a 中username为基准 找出filename字段中与username字段相同字的个数查询结果如下：username filename num
张三 张明 1
张三 李四 0
张三 赵六 0
李四 张明 0
李四 李四 2
李四 赵六 0
王五 张明 0
王五 李四 0
王五 赵六 0请各位大侠高手帮帮忙！！
username
张三
李四
王五
表 b filename
张明
李四
赵六然后以表 a 中username为基准 找出filename字段中与username字段相同字的个数查询结果如下：username filename num
张三 张明 1
张三 李四 0
张三 赵六 0
李四 张明 0
李四 李四 2
李四 赵六 0
王五 张明 0
王五 李四 0
王五 赵六 0请各位大侠高手帮帮忙！！
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (username VARCHAR(4))
INSERT INTO @tb1
SELECT '张三' UNION ALL
SELECT '李四' UNION ALL
SELECT '王五'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (filename VARCHAR(4))
INSERT INTO @tb2
SELECT '张明' UNION ALL
SELECT '李四' UNION ALL
SELECT '赵六'--SQL查询如下:;WITH Liang AS
(
SELECT TOP(50)
ID=ROW_NUMBER() OVER(ORDER BY o.[object_id])
FROM sys.objects AS o,sys.columns
),
Liang2 AS
(
SELECT
username,
SUBSTRING(username,B.ID,1) AS word
FROM @tb1 AS A
JOIN Liang AS B
ON SUBSTRING(A.username,B.ID,1)<>''
),
Liang3 AS
(
SELECT
A.filename,
SUBSTRING(filename,B.ID,1) AS word
FROM @tb2 AS A
JOIN Liang AS B
ON SUBSTRING(A.filename,B.ID,1)<>''
)
SELECT
A.username,
B.filename,
SUM(CASE WHEN A.word=B.word THEN 1 ELSE 0 END) AS num
FROM Liang2 AS A,Liang3 AS B
GROUP BY A.username,B.filename
ORDER BY username,filename/*
username filename num
-------- -------- -----------
李四 李四 2
李四 张明 0
李四 赵六 0
王五 李四 0
王五 张明 0
王五 赵六 0
张三 李四 0
张三 张明 1
张三 赵六 0(9 行受影响)
*/
因为他们都有一个'张'字！
只要两个字段记录中有几个相同的字 num字段 显示为 几比如北京市朝阳区 北京市昌平区 4
create function f_str(@name1 varchar(20),@name2 varchar(20))
returns int
as
begin
declare @int int
set @int=0
while len(@name1)>0 and len(@name2)>0
begin
if left(@name1,1)=left(@name2,1)
set @int=@int+1
select @name1=stuff(@name1,1,1,''),@name2=stuff(@name2,1,1,'')
end
return @int
end
godeclare @a table(username varchar(20))
insert into @a select '张三'
insert into @a select '李四'
insert into @a select '王五'
declare @b table(filename varchar(20))
insert into @b select '张明'
insert into @b select '李四'
insert into @b select '赵六' select *,dbo.f_str(username,filename) as num from @a,@b
/*
username filename num
-------------------- -------------------- -----------
张三 张明 1
李四 张明 0
王五 张明 0
张三 李四 0
李四 李四 2
王五 李四 0
张三 赵六 0
李四 赵六 0
王五 赵六 0
*/
godrop function f_str
go
那就修改一下：
create function f_str(@name1 varchar(20),@name2 varchar(20))
returns int
as
begin
declare @int int
set @int=0
while len(@name1)>0 and len(@name2)>0
begin
if charindex(left(@name1,1),@name2)>0
set @int=@int+1
select @name2=stuff(@name2,charindex(left(@name1,1),@name2),1,''),@name1=stuff(@name1,1,1,'')
end
return @int
end
godeclare @a table(username varchar(20))
insert into @a select '张三'
insert into @a select '李四'
insert into @a select '王五'
declare @b table(filename varchar(20))
insert into @b select '三张'
insert into @b select '李四'
insert into @b select '赵六' select *,dbo.f_str(username,filename) as num from @a a,@b b order by a.username,b.filename
/*
username filename num
-------------------- -------------------- -----------
李四 李四 2
李四 三张 0
李四 赵六 0
王五 李四 0
王五 三张 0
王五 赵六 0
张三 李四 0
张三 三张 2
张三 赵六 0
*/
godrop function f_str
go
IF NOT OBJECT_ID('TB') IS NULL DROP TABLE TB
GO
CREATE TABLE TB([USERNAME] NVARCHAR(2))
Insert tb
SELECT N'张三' UNION ALL
SELECT N'李四' UNION ALL
SELECT N'王五'
Go
IF NOT OBJECT_ID('TC') IS NULL DROP TABLE TC
GO
CREATE TABLE TC([FILENAME] NVARCHAR(2))
Insert tc
SELECT N'张明' UNION ALL
SELECT N'李四' UNION ALL
SELECT N'赵六'
GoIF NOT OBJECT_ID('F_CHECKF') IS NULL DROP FUNCTION F_CHECKF
GO
CREATE FUNCTION F_CHECKF(@USERNAME VARCHAR(20),@FILENAME VARCHAR(20))
RETURNS INT
AS
BEGIN
DECLARE @STR INT,@RE INT
SET @RE=0
WHILE LEN(@USERNAME)>0
BEGIN
SET @STR=CHARINDEX(LEFT(@USERNAME,1),@FILENAME)
IF @STR>0
SELECT @FILENAME=STUFF(@FILENAME,@STR,1,''),@RE=@RE+1
SET @USERNAME=STUFF(@USERNAME,1,1,'')
END
RETURN @RE
END
GOSELECT *,
NUM=DBO.F_CHECKF(USERNAME,FILENAME)
FROM TB
CROSS JOIN TC/*
USERNAME FILENAME NUM
-------- -------- -----------
张三 张明 1
李四 张明 0
王五 张明 0
张三 李四 0
李四 李四 2
王五 李四 0
张三 赵六 0
李四 赵六 0
王五 赵六 0（所影响的行数为 9 行）*/
drop function [dbo].[f_count]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[a]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[b]
GOcreate function f_count(@username varchar(100),@filename varchar(100))
returns int
as
begin
declare @len int,@temp int,@count int
select @temp=1,@count=0
if(len(@username)<=len(@filename)) set @len=len(@username)
else set @len=len(@filename) while(@temp<=@len)
begin
if(substring(@username,@temp,1)=substring(@filename,@temp,1))
set @count=@count+1 set @temp=@temp+1 end
return @count
end
gocreate table a(username varchar(10))
insert a select '张三'
insert a select '李四'
insert a select '王五'
gocreate table b([filename] varchar(10))
insert b select '张明'
insert b select '李四'
insert b select '赵六'
goselect * from a
select * from bselect username,[filename],[count]=dbo.f_count(username,[filename])
from a cross join bdrop table a,b
drop function dbo.f_count/*（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）username
----------
张三
李四
王五（所影响的行数为 3 行）filename
----------
张明
李四
赵六（所影响的行数为 3 行）username filename count
---------- ---------- -----------
张三 张明 1
李四 张明 0
王五 张明 0
张三 李四 0
李四 李四 2
王五 李四 0
张三 赵六 0
李四 赵六 0
王五 赵六 0（所影响的行数为 9 行）*/
-- Author: happyflystone
-- Date : 2009-02-12 16:58:33
-- 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: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(username NVARCHAR(2))
Go
INSERT INTO ta
SELECT '张三' UNION ALL
SELECT '李四' UNION ALL
SELECT '王五'
GO
-- Test Data: tb
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(filename NVARCHAR(2))
Go
INSERT INTO tb
SELECT '张明' UNION ALL
SELECT '李四' UNION ALL
SELECT '赵六'
GO
--Start
;with t
as
(select n= row_number() over (order by getdate()) from sys.sysobjects)
,t1
as(
SELECT
*
FROM
TA as a
full join tb as c on 1 =1
CROSS APPLY (
SELECT CardID=(
SELECT
',' AS [text()]
FROM tb,t
WHERE charindex(substring(filename,n,1),A.username) > 0 and c.filename = filename
FOR XML PATH('')
)
) AS B
)
select username,filename,isnull(len(cardid),0) as num from t1
--Result:
/*username filename num
-------- -------- --------------------
张三 张明 1
张三 李四 0
张三 赵六 0
李四 张明 0
李四 李四 2
李四 赵六 0
王五 张明 0
王五 李四 0
王五 赵六 0(9 行受影响)
*/
--End