22,209
社区成员
发帖
与我相关
我的任务
分享
DECLARE @a VARCHAR(20),
@b INT,
@c VARCHAR(20),
@d VARCHAR(20),
@e VARCHAR(20)
SET @a='刘八'
SET @b=25
SET @c='公司1'
SET @d='市场部'
SET @e='看书'
SELECT a.*,
CASE when a.name=@a THEN 1 else 0 END+
CASE when a.age=@b THEN 1 else 0 END+
CASE when a.company=@c THEN 1 else 0 END+
CASE when a.department=@d THEN 1 else 0 END+
CASE when a.hobby=@e THEN 1 else 0 END AS 级别
FROM #tab a
自己变通一下嘛,这个表和变量有啥区别啊[/quote]
DECLARE @a VARCHAR(20),
@b INT,
@c VARCHAR(20),
@d VARCHAR(20),
@e VARCHAR(20)
SET @a='刘八'
SET @b=25
SET @c='公司1'
SET @d='市场部'
SET @e='看书'
SELECT a.*,
CASE when a.name=@a THEN 1 else 0 END+
CASE when a.age=@b THEN 1 else 0 END+
CASE when a.company=@c THEN 1 else 0 END+
CASE when a.department=@d THEN 1 else 0 END+
CASE when a.hobby=@e THEN 1 else 0 END AS 级别
FROM #tab a
自己变通一下嘛,这个表和变量有啥区别啊[/quote]
嗯嗯 刚刚测试写错了,可以了
DECLARE @a VARCHAR(20),
@b INT,
@c VARCHAR(20),
@d VARCHAR(20),
@e VARCHAR(20)
SET @a='刘八'
SET @b=25
SET @c='公司1'
SET @d='市场部'
SET @e='看书'
SELECT a.*,
CASE when a.name=@a THEN 1 else 0 END+
CASE when a.age=@b THEN 1 else 0 END+
CASE when a.company=@c THEN 1 else 0 END+
CASE when a.department=@d THEN 1 else 0 END+
CASE when a.hobby=@e THEN 1 else 0 END AS 级别
FROM #tab a
自己变通一下嘛,这个表和变量有啥区别啊
use Tempdb
go
--> --> 听雨停了-->生成测试数据
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([id] int,[name] nvarchar(22),[age] int,[company] nvarchar(23),[department] nvarchar(23),[hobby] nvarchar(23))
Insert #tab
select 1,N'张三',25,N'公司1',N'市场部',N'看书' union all
select 2,N'李四',25,N'公司1',N'软件部',N'编程' union all
select 3,N'王五',25,N'公司1',N'市场部',N'羽毛球' union all
select 4,N'赵六',27,N'公司3',N'软件部',N'看书' union all
select 5,N'钱七',26,N'公司2',N'市场部',N'看书'
Go
if not object_id(N'Tempdb..#tab1') is null
drop table #tab1
Go
Create table #tab1([name] nvarchar(22),[age] int,[company] nvarchar(23),[department] nvarchar(23),[hobby] nvarchar(22))
Insert #tab1
select N'刘八',25,N'公司1',N'市场部',N'看书'
Go
--测试数据结束
SELECT a.*,
CASE when a.name=b.name THEN 1 else 0 END+
CASE when a.age=b.age THEN 1 else 0 END+
CASE when a.company=b.company THEN 1 else 0 END+
CASE when a.department=b.department THEN 1 else 0 END+
CASE when a.hobby=b.hobby THEN 1 else 0 END AS 级别
FROM #tab a,#tab1 b