CREATE PROC p @name VARCHAR(20) AS SELECT A.试题内容, A.试题的发布日期 A.试题的状态, ISNULL(COUNT(B.subjectid),0) AS 试题的报名人数 FROM subjectinfo AS A LEFT JOIN signinfo AS B ON A.subjectid=B.subjectid WHERE A.name=@name GROUP BY A.试题内容, A.试题的发布日期 A.试题的状态; GO
这个我知道,能说的具体一些吗?最还把整个SQL语句写出来,谢谢了
建议在前台做这个..CREATE PROC p @name VARCHAR(20) AS SELECT A.试题内容, A.试题的发布日期 CASE A.试题的状态 WHEN 0 THEN '待审核' WHEN 1 THEN '未通过' WHEN 2 THEN '已通过审核' END AS 试题的状态, ISNULL(COUNT(B.subjectid),0) AS 试题的报名人数 FROM subjectinfo AS A LEFT JOIN signinfo AS B ON A.subjectid=B.subjectid WHERE A.name=@name GROUP BY A.试题内容, A.试题的发布日期 A.试题的状态; GO
CREATE PROC p @name VARCHAR(20) AS SELECT A.试题内容, A.试题的发布日期 CASE A.试题的状态 WHEN 0 THEN '待审核' WHEN 1 THEN '未通过' WHEN 2 THEN '已通过审核' END AS 试题的状态, ISNULL(COUNT(B.subjectid),0) AS 试题的报名人数 FROM subjectinfo AS A LEFT JOIN signinfo AS B ON A.subjectid=B.subjectid WHERE A.name=@name GROUP BY A.试题内容, A.试题的发布日期 A.试题的状态; GO我刚才那样弄了可是提示这样的错误: 不能比较或排序 text、ntext 和 image 数据类型,除非使用 IS NULL 或 LIKE 运算符。 试题的内容那个字段是ntext类型的,把GROUP BY 中的A.试题内容去掉后有提示A.试题内容无效的错误
CREATE PROC p @name VARCHAR(20) AS SELECT A.试题内容, A.试题的发布日期 CASE A.试题的状态 WHEN 0 THEN '待审核' WHEN 1 THEN '未通过' WHEN 2 THEN '已通过审核' END AS 试题的状态, ISNULL(B.试题的报名人数,0) AS 试题的报名人数 FROM subjectinfo AS A LEFT JOIN ( SELECT subjectid,COUNT(*) AS 试题的报名人数 FROM signinfo GROUP BY subjectid ) AS B ON A.subjectid=B.subjectid; GO
漏了那个 WHERE name=@name了.自己加上.
看看这个行不行,特别注意其中的解释:create table dbo.subjectinfo ( subjectid int identity(1,1)--[试题的ID] ,content nvarchar(max)--[试题内容] ,state int--[试题状态0-待审核 1-未通过审核 2-已通过审核] ,date datetime--[试题的发布日期] ,name nvarchar(25)--[试题的命题人] )insert into dbo.subjectinfo select N'Test1111',0,getdate(),N'张三'insert into dbo.subjectinfo select N'Test1111',0,getdate(),N'张三'select * from dbo.subjectinfocreate table dbo.signinfo ( ID int identity(1,1) --(报名ID) ,subjectid int--[所选试题的ID] ,name nvarchar(25)--[报名者姓名] )insert into dbo.signinfo select 1,N'报名者' insert into dbo.signinfo select 2,N'报名者1' insert into dbo.signinfo select 2,N'报名者2' select * from dbo.signinfo GOcreate proc dbo.up_GetInfo @name nvarchar(25) as set nocount on --判断某个老师有没有出题或者是由没有被选 if exists(select top 1 1 from dbo.signinfo as a with (Nolock) inner join dbo.subjectinfo as b with (Nolock) on a.subjectid=b.subjectid where b.name=@name ) begin select a.content AS [试题内容] ,convert(char(10),a.date,120) As [试题的发布日期] , CASE A.state WHEN 0 THEN '待审核' WHEN 1 THEN '未通过' WHEN 2 THEN '已通过审核' END as [试题的状态] ,a.name as [试题的命题人] ,ISNULL(count(b.subjectid),0) AS [试题的报名人数] from dbo.signinfo as b with (Nolock) inner join dbo.subjectinfo as a with (Nolock)--这个地方使用INNER JOIN就可以了 on a.subjectid=b.subjectid where a.name=@name /* --这个地方一定是要Group的,因为有可能一个老师出了多套试题. 如果像你所说那样子"不能比较或排序 text、ntext 和 image 数据类型,除非使用 IS NULL 或 LIKE 运算符" 我是建议在设计表结构的时候,在此存另一个试卷内容表的表主键,也就是多加一个试卷内容表。 */ group by a.content ,convert(char(10),a.date,120)--Group By时间字段,一般都是按照天,如果精确到秒的话,应该没有多大的意义 ,a.name ,CASE A.state WHEN 0 THEN '待审核' WHEN 1 THEN '未通过' WHEN 2 THEN '已通过审核' END end ELSE --这个表示没有出题或者没有被选,手动得到报名人数为0,否则会没有结果集 BEGIN select NULL as [试题内容] ,NULL As [试题的发布日期] ,NULL [试题的状态] ,NULL as [试题的命题人] ,0 AS [试题的报名人数] END GO --测试 EXEC dbo.up_GetInfo N'张三'
AS
SELECT
A.试题内容,
A.试题的发布日期
A.试题的状态,
ISNULL(COUNT(B.subjectid),0) AS 试题的报名人数
FROM subjectinfo AS A
LEFT JOIN signinfo AS B
ON A.subjectid=B.subjectid
WHERE A.name=@name
GROUP BY
A.试题内容,
A.试题的发布日期
A.试题的状态;
GO
建议在前台做这个..CREATE PROC p @name VARCHAR(20)
AS
SELECT
A.试题内容,
A.试题的发布日期
CASE A.试题的状态
WHEN 0 THEN '待审核'
WHEN 1 THEN '未通过'
WHEN 2 THEN '已通过审核'
END AS 试题的状态,
ISNULL(COUNT(B.subjectid),0) AS 试题的报名人数
FROM subjectinfo AS A
LEFT JOIN signinfo AS B
ON A.subjectid=B.subjectid
WHERE A.name=@name
GROUP BY
A.试题内容,
A.试题的发布日期
A.试题的状态;
GO
AS
SELECT
A.试题内容,
A.试题的发布日期
CASE A.试题的状态
WHEN 0 THEN '待审核'
WHEN 1 THEN '未通过'
WHEN 2 THEN '已通过审核'
END AS 试题的状态,
ISNULL(COUNT(B.subjectid),0) AS 试题的报名人数
FROM subjectinfo AS A
LEFT JOIN signinfo AS B
ON A.subjectid=B.subjectid
WHERE A.name=@name
GROUP BY
A.试题内容,
A.试题的发布日期
A.试题的状态;
GO我刚才那样弄了可是提示这样的错误:
不能比较或排序 text、ntext 和 image 数据类型,除非使用 IS NULL 或 LIKE 运算符。
试题的内容那个字段是ntext类型的,把GROUP BY 中的A.试题内容去掉后有提示A.试题内容无效的错误
AS
SELECT
A.试题内容,
A.试题的发布日期
CASE A.试题的状态
WHEN 0 THEN '待审核'
WHEN 1 THEN '未通过'
WHEN 2 THEN '已通过审核'
END AS 试题的状态,
ISNULL(B.试题的报名人数,0) AS 试题的报名人数
FROM subjectinfo AS A
LEFT JOIN
(
SELECT subjectid,COUNT(*) AS 试题的报名人数
FROM signinfo
GROUP BY subjectid
) AS B
ON A.subjectid=B.subjectid;
GO
WHERE name=@name了.自己加上.
(
subjectid int identity(1,1)--[试题的ID]
,content nvarchar(max)--[试题内容]
,state int--[试题状态0-待审核 1-未通过审核 2-已通过审核]
,date datetime--[试题的发布日期]
,name nvarchar(25)--[试题的命题人]
)insert into dbo.subjectinfo
select N'Test1111',0,getdate(),N'张三'insert into dbo.subjectinfo
select N'Test1111',0,getdate(),N'张三'select * from dbo.subjectinfocreate table dbo.signinfo
(
ID int identity(1,1) --(报名ID)
,subjectid int--[所选试题的ID]
,name nvarchar(25)--[报名者姓名]
)insert into dbo.signinfo
select 1,N'报名者'
insert into dbo.signinfo
select 2,N'报名者1'
insert into dbo.signinfo
select 2,N'报名者2'
select * from dbo.signinfo
GOcreate proc dbo.up_GetInfo
@name nvarchar(25)
as
set nocount on
--判断某个老师有没有出题或者是由没有被选
if exists(select top 1 1
from dbo.signinfo as a with (Nolock)
inner join dbo.subjectinfo as b with (Nolock)
on a.subjectid=b.subjectid
where b.name=@name
)
begin
select a.content AS [试题内容]
,convert(char(10),a.date,120) As [试题的发布日期]
, CASE A.state
WHEN 0 THEN '待审核'
WHEN 1 THEN '未通过'
WHEN 2 THEN '已通过审核'
END as [试题的状态]
,a.name as [试题的命题人]
,ISNULL(count(b.subjectid),0) AS [试题的报名人数]
from dbo.signinfo as b with (Nolock)
inner join dbo.subjectinfo as a with (Nolock)--这个地方使用INNER JOIN就可以了
on a.subjectid=b.subjectid
where a.name=@name
/*
--这个地方一定是要Group的,因为有可能一个老师出了多套试题.
如果像你所说那样子"不能比较或排序 text、ntext 和 image 数据类型,除非使用 IS NULL 或 LIKE 运算符"
我是建议在设计表结构的时候,在此存另一个试卷内容表的表主键,也就是多加一个试卷内容表。
*/
group by a.content
,convert(char(10),a.date,120)--Group By时间字段,一般都是按照天,如果精确到秒的话,应该没有多大的意义
,a.name
,CASE A.state
WHEN 0 THEN '待审核'
WHEN 1 THEN '未通过'
WHEN 2 THEN '已通过审核'
END
end
ELSE --这个表示没有出题或者没有被选,手动得到报名人数为0,否则会没有结果集
BEGIN
select NULL as [试题内容]
,NULL As [试题的发布日期]
,NULL [试题的状态]
,NULL as [试题的命题人]
,0 AS [试题的报名人数]
END
GO
--测试
EXEC dbo.up_GetInfo N'张三'