怎样实现把第一条SQL语句的结果作为第二条SQL语句的参数
第一条SQL语句 select MANAGER from Department where DEPT_ID in (select DEPT_ID from [User] where [USER_ID]='lisi')第二条SQL语句 select * from Diary where [USER_ID] in ('第一条SQL语句') ----括号里是字符串 --------------------编程问答-------------------- 写存储过程 --------------------编程问答-------------------- 急......... --------------------编程问答-------------------- 怎么写存储过程啊 求教 --------------------编程问答-------------------- select * from Diary where [USER_ID] in (select MANAGER from Department where DEPT_ID in (select DEPT_ID from [User] where [USER_ID]='lisi')
)
你直接这样查不出结果么？
子查询，只要你第一个查询的结果是正确的，那么这样就不会有错
每个子查询你要去试试是不是都是你想要的那个结果。
一个套一个结果自然就出来了 --------------------编程问答--------------------
select * from Diary where [USER_ID] in（''） 里面是字符串 不能这么写 --------------------编程问答--------------------
怎么写存储过程 0.0 --------------------编程问答-------------------- declare @str1
set @str1 = 'select MANAGER from Department where DEPT_ID in (select DEPT_ID from [User] where [USER_ID]=''lisi'')'
declare @str2
set @str2 = 'select * from Diary where [USER_ID] in (' + @str1 + ')'
exec(@str2)
这个意思?? --------------------编程问答--------------------
set 附近有语法错误 --------------------编程问答-------------------- declare @str2 后面没有加数据类型.
ex: declare @str2 varchar(50) --------------------编程问答-------------------- Test Data
--------------------编程问答--------------------
IF OBJECT_ID('UDepartment') IS NOT NULL
DROP TABLE UDepartment
---部门表
CREATE TABLE UDepartment
(
DEPT_ID INT PRIMARY KEY NOT NULL,
DEPT_Name VARCHAR(30),
MANAGER VARCHAR(30)
)
INSERT INTO UDepartment
SELECT 1,'A','M1' UNION ALL
SELECT 2,'B','M2' UNION ALL
SELECT 3,'C','M3'
SELECT * FROM UDepartment
/*
DEPT_ID DEPT_Name MANAGER
----------- ------------------------------ ------------------------------
1 A M1
2 B M2
3 C M3
(3 行受影响)
*/
IF OBJECT_ID('[User]') IS NOT NULL
DROP TABLE [User]
CREATE TABLE [User]
(
[USER_ID] VARCHAR(20) PRIMARY KEY NOT NULL,
DEPT_ID INT FOREIGN KEY REFERENCES UDepartment(DEPT_ID),
[USER_Name] VARCHAR(30)
)
INSERT INTO [User]
SELECT 'lisi',1,'U1' UNION ALL
SELECT 'zhangsan',2,'U2' UNION ALL
SELECT 'wangwu',3,'U3'
SELECT * FROM [User]
/*
USER_ID DEPT_ID USER_Name
-------------------- ----------- ------------------------------
lisi 1 U1
wangwu 3 U3
zhangsan 2 U2
(3 行受影响)
*/
IF OBJECT_ID('Diary') IS NOT NULL
DROP TABLE Diary
CREATE TABLE Diary
(
Diary_ID INT PRIMARY KEY NOT NULL,
MANAGER VARCHAR(30),
[USER_ID] VARCHAR(20) FOREIGN KEY REFERENCES [User]([USER_ID])
)
INSERT INTO Diary
SELECT 1,'M1','lisi' UNION ALL
SELECT 2,'M2','zhangsan' UNION ALL
SELECT 3,'M3','wangwu'
SELECT * FROM Diary
/*
Diary_ID MANAGER USER_ID
----------- ------------------------------ --------------------
1 M1 lisi
2 M2 zhangsan
3 M3 wangwu
(3 行受影响)
*/
--操作
declare @str1 VARCHAR(50)
set @str1=(SELECT MANAGER FROM UDepartment where DEPT_ID in (select DEPT_ID from [User] where [USER_ID]='lisi'))
PRINT @str1
select * from Diary where MANAGER in (@str1)
/*
Diary_ID MANAGER USER_ID
----------- ------------------------------ --------------------
1 M1 lisi
(1 行受影响)
*/
把括号里的引号去掉也不行？ --------------------编程问答-------------------- 上面的已经帮你解决了…… top --------------------编程问答-------------------- 第一个sql语句的参数设置为输出参数 --------------------编程问答--------------------
申明变量都不给类型的，你太强大了
--------------------编程问答--------------------
还没搞定就去sql查询分析器里面，一句一句执行，然后看看都什么结果，就知道怎么弄了补充：.NET技术 , ASP.NET
