我要是写 select * from catalog where id in(5,6,7) 能够查到 但写 select * from catalog where id in (select qx from user where name=m) 就不行 select qx from user where name=m 查到的内容不就是 5,6,7 嘛 不懂啦 求助
declare @t table (id int,nr varchar(20))declare @t1 table (id int,name varchar(20),qx varchar(20))insert @t values (5,'aa') insert @t values (6,'bb') insert @t values (7,'cc')insert @t1 values (1,'m','5,6,7') insert @t1 values (2,'n','6,7') select * from @t where charindex(cast(id as varchar),cast((select qx from @t1 where name='n') as varchar))>0 select * from @t where charindex(cast(id as varchar),cast((select qx from @t1 where name='m') as varchar))>0 (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行) (所影响的行数为 1 行)id nr ----------- -------------------- 6 bb 7 cc(所影响的行数为 2 行)id nr ----------- -------------------- 5 aa 6 bb 7 cc(所影响的行数为 3 行)
同意楼上,关键在于对t_sql的函数cast,charindex的理解
非常谢谢两位朋友的帮助,但还是有点问题,真的谢谢你们了 如果catalog表变大些,如 id nr 5 a 6 b 7 c 8 d 9 e 10 f 11 g 12 h 13 i 14 j 15 k 16 l 17 m 18 n user表变成 id name qx 1 m 5,6,7,8,9,10,11,12,13,14,15,16,17,18 而结果只能查到10条,为什么啊 select * from catalog where (charindex(cast(id)as varchar,cast((select qx from user where id=1)as varchar))>0; 谢谢嫩的帮助
此时候必须对varchar设定一个值。cast((select qx from @t1 where name='m') as varchar(200))declare @t table (id int,nr varchar(20))declare @t1 table (id int,name varchar(20),qx varchar(80))insert @t values (5,'aa') insert @t values (6,'bb') insert @t values (7,'cc') insert @t values (8,'dd') insert @t values (9,'ee') insert @t values (10,'ff') insert @t values (11,'aa') insert @t values (12,'bb') insert @t values (13,'cc') insert @t values (14,'mm') insert @t values (15,'cc') insert @t values (16,'aa') insert @t values (17,'bb') insert @t values (18,'cc')insert @t1 values (1,'m','5,6,7,8,9,10,11,12,13,14,15,16,17,18') insert @t1 values (2,'n','6,7')select * from @t where charindex(cast(id as varchar(200)),cast((select qx from @t1 where name='m') as varchar(200)))>0 (所影响的行数为 1 行)id nr ----------- -------------------- 5 aa 6 bb 7 cc 8 dd 9 ee 10 ff 11 aa 12 bb 13 cc 14 mm 15 cc 16 aa 17 bb 18 cc(所影响的行数为 14 行)
例如,一个用户n登陆系统,他的权限是6,7
然后查询catalog表里权限是6,7里的内容.
很简单的内容,
再求助
select * from catalog where id in(5,6,7)
能够查到
但写
select * from catalog where id in (select qx from user where name=m)
就不行
select qx from user where name=m
查到的内容不就是
5,6,7
嘛
不懂啦
求助
(id int,nr varchar(20))declare @t1 table
(id int,name varchar(20),qx varchar(20))insert @t values (5,'aa')
insert @t values (6,'bb')
insert @t values (7,'cc')insert @t1 values (1,'m','5,6,7')
insert @t1 values (2,'n','6,7')
select * from @t where
charindex(cast(id as varchar),cast((select qx from @t1 where name='n') as varchar))>0 select * from @t where
charindex(cast(id as varchar),cast((select qx from @t1 where name='m') as varchar))>0
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)id nr
----------- --------------------
6 bb
7 cc(所影响的行数为 2 行)id nr
----------- --------------------
5 aa
6 bb
7 cc(所影响的行数为 3 行)
如果catalog表变大些,如
id nr
5 a
6 b
7 c
8 d
9 e
10 f
11 g
12 h
13 i
14 j
15 k
16 l
17 m
18 n
user表变成
id name qx
1 m 5,6,7,8,9,10,11,12,13,14,15,16,17,18
而结果只能查到10条,为什么啊
select * from catalog where (charindex(cast(id)as varchar,cast((select qx from user where
id=1)as varchar))>0;
谢谢嫩的帮助
(id int,nr varchar(20))declare @t1 table
(id int,name varchar(20),qx varchar(80))insert @t values (5,'aa')
insert @t values (6,'bb')
insert @t values (7,'cc')
insert @t values (8,'dd')
insert @t values (9,'ee')
insert @t values (10,'ff')
insert @t values (11,'aa')
insert @t values (12,'bb')
insert @t values (13,'cc')
insert @t values (14,'mm')
insert @t values (15,'cc')
insert @t values (16,'aa')
insert @t values (17,'bb')
insert @t values (18,'cc')insert @t1 values (1,'m','5,6,7,8,9,10,11,12,13,14,15,16,17,18')
insert @t1 values (2,'n','6,7')select * from @t where
charindex(cast(id as varchar(200)),cast((select qx from @t1 where name='m') as varchar(200)))>0 (所影响的行数为 1 行)id nr
----------- --------------------
5 aa
6 bb
7 cc
8 dd
9 ee
10 ff
11 aa
12 bb
13 cc
14 mm
15 cc
16 aa
17 bb
18 cc(所影响的行数为 14 行)