如下是我的存储过程, 想让它最终返回表TC的内容, 请高手御笔修改! set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[chaos]
AS
BEGIN
SELECT * INTO # FROM (
SELECT ID,num,question,choiceA FROM TB UNION ALL
SELECT ID,num,question,choiceB FROM TB UNION ALL
SELECT ID,num,question,choiceC FROM TB UNION ALL
SELECT ID,num,question,choiceD FROM TB
) A
ORDER BY ID,NEWID()
SELECT *,SEQ=IDENTITY(INT,1,1) INTO #T FROM #
TRUNCATE TABLE TC
INSERT TC(num,question,choiceA,choiceB,choiceC,choiceD,answer)
SELECT num,TC.question,choiceA,choiceB,choiceC,choiceD,
CASE WHEN choiceA=choice THEN 'A' WHEN choiceB=choice THEN 'B' WHEN choiceC=choice THEN 'C' WHEN choiceD=choice THEN 'D' END AS answer
FROM (SELECT ID,num,question,
MAX(CASE WHEN SEQ=1 THEN choiceA END) AS choiceA,
MAX(CASE WHEN SEQ=2 THEN choiceA END) AS choiceB,
MAX(CASE WHEN SEQ=3 THEN choiceA END) AS choiceC,
MAX(CASE WHEN SEQ=4 THEN choiceA END) AS choiceD
FROM (SELECT ID,num,question,choiceA,SEQ-(SELECT COUNT(*) FROM #T WHERE ID<A.ID) AS SEQ FROM #T AS A) T
GROUP BY ID,num,question) AS TC
JOIN
(SELECT ID,CASE ANSWER WHEN 'A' THEN choiceA WHEN 'B' THEN choiceB WHEN 'C' THEN choiceC WHEN 'D' THEN choiceD END AS choice
FROM TB) AS TB
ON TC.ID=TB.ID
ORDER BY NEWID()
SELECT * FROM TC
DROP TABLE #
DROP TABLE #T
END
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[chaos]
AS
BEGIN
SELECT * INTO # FROM (
SELECT ID,num,question,choiceA FROM TB UNION ALL
SELECT ID,num,question,choiceB FROM TB UNION ALL
SELECT ID,num,question,choiceC FROM TB UNION ALL
SELECT ID,num,question,choiceD FROM TB
) A
ORDER BY ID,NEWID()
SELECT *,SEQ=IDENTITY(INT,1,1) INTO #T FROM #
TRUNCATE TABLE TC
INSERT TC(num,question,choiceA,choiceB,choiceC,choiceD,answer)
SELECT num,TC.question,choiceA,choiceB,choiceC,choiceD,
CASE WHEN choiceA=choice THEN 'A' WHEN choiceB=choice THEN 'B' WHEN choiceC=choice THEN 'C' WHEN choiceD=choice THEN 'D' END AS answer
FROM (SELECT ID,num,question,
MAX(CASE WHEN SEQ=1 THEN choiceA END) AS choiceA,
MAX(CASE WHEN SEQ=2 THEN choiceA END) AS choiceB,
MAX(CASE WHEN SEQ=3 THEN choiceA END) AS choiceC,
MAX(CASE WHEN SEQ=4 THEN choiceA END) AS choiceD
FROM (SELECT ID,num,question,choiceA,SEQ-(SELECT COUNT(*) FROM #T WHERE ID<A.ID) AS SEQ FROM #T AS A) T
GROUP BY ID,num,question) AS TC
JOIN
(SELECT ID,CASE ANSWER WHEN 'A' THEN choiceA WHEN 'B' THEN choiceB WHEN 'C' THEN choiceC WHEN 'D' THEN choiceD END AS choice
FROM TB) AS TB
ON TC.ID=TB.ID
ORDER BY NEWID()
SELECT * FROM TC
DROP TABLE #
DROP TABLE #T
END
解决方案 »
- 为什么有主键ID了,还要用uniqueidentifier?
- 明明有记录 但用 sp_executesql查询不出来
- 帮忙调试个简单的存储过程,头晕了,谢谢!
- 菜鸟问题,这个sql语句怎么写?
- SQL链接服务器 检索 创建视图出错
- 如何将 sql server 2000 中的图象数据保存到磁盘文件?
- SQL排序的经典问题?在线及等!!!!!!!!!!!!!!1
- SQL6.5 + WIN2K + ADO2.0 ----- 简单问题
- 我插入值时出现:"当 IDENTITY_INSERT 设置为 OFF 时,不能向表 'student' 中的标识列插入显式值。"
- Named Pipes Provider: Could not open a connection to SQL Server [1326].
- SQL函数大全
- 这么简单的SQL语句,怎么有问题呢?100分相送。
DROP TABLE #
DROP TABLE #T
SELECT * FROM TC
END
DROP TABLE #
DROP TABLE #TThis part can be shrinkaged to SELECT * FROM TC
SELECT * FROM TC
DROP TABLE #
DROP TABLE #T
改成了
DROP TABLE #
DROP TABLE #T
SELECT * FROM TC
我想是对了,但是为什么
select * from exec chaos
报错?
select * from exec chaos
应改成什么样?
insert tbs
exec chaos