关于DISTINCT问题请教

[mashixuan]

我现在的SQL语句是
select DISTINCT month(OperateTime) as month,Type,count(*) as id from CarrierLog
group by Type,month(OperateTime)
得到结果如下：
month Type id
6 开户 2
6 暂停 4
6 注销 8
7 开户 2
7 暂停 2
7 注销 2
7 删除 2
我想得到结果是
month Type id
6 开户 2
暂停 4
注销 8
7 开户 2
暂停 2
注销 2
删除 2
请问我的SQL语句怎么修改

[hyde100]

友情帮顶

[nzperfect]

用程序搞定不好吗？

[mashixuan]

麻烦能说具体点吗 我在后台StringBuilder变量存值 页面端<%=变量取值%>

[jasonren]

感觉还是在程序上下功夫比较好.

[叶子]

declare @mm table (month int,Type varchar(4),id int)
insert into @mm
select 6,'开户',2 union all
select 6,'暂停',4 union all
select 6,'注销',8 union all
select 7,'开户',2 union all
select 7,'暂停',2 union all
select 7,'注销',2 union all
select 7,'删除',2
--select * from @mm
UPDATE @mm
SET month=NULL
WHERE Type!='开户'
select * from @mm

/*
在你的结果上处理一下
*/

/*
month       Type id
----------- ---- -----------
6           开户   2
NULL        暂停   4
NULL        注销   8
7           开户   2
NULL        暂停   2
NULL        注销   2
NULL        删除   2
*/

[mashixuan]

我发下我后台的代码你们看下
SqlDataReader rdr = null;
string Strsql = "select DISTINCT month(OperateTime) as month,Type,count(*) as id from CarrierLog where Type!='登录' and Type!='退出' group by Type,month(OperateTime)";
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["CCOD"].ConnectionString);

SqlCommand sqlcmd = new SqlCommand(Strsql,sqlcon);
try
{
sqlcon.Open();
rdr = sqlcmd.ExecuteReader();

while (rdr.Read())
{
buid.Append("<tr>");
buid.Append("<td>");
buid.Append((string)rdr["Type"]);
buid.Append("</td>");
buid.Append("</tr>\n");
strType=buid.ToString();
buid1.Append("<tr>");
buid1.Append("<td>");
buid1.Append((int)rdr["id"]);
buid1.Append("</td>");
buid1.Append("</tr>\n");
buid2.Append("<tr>");
buid2.Append("<td>");
strCount = buid1.ToString() ;
buid2.Append((int)rdr["month"]);
buid2.Append("</td>");
buid2.Append("</tr>\n");
strTime = buid2.ToString();
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}

if (sqlcon != null)
{
sqlcon.Close();
}
}

页面端
<table style="width: 839px; height: 138px" border="1">
<tr>
<td style="width: 100px; text-align: center">
月份</td>
<td style="width: 100px; text-align: center">
状态</td>
<td style="width: 100px; text-align: center">
次数</td>
</tr>
<tr>
<td style="width: 100px; height: 66px;"><table>
<%= strTime.ToString()%></table>
</td>
<td style="width: 100px; height: 66px;"><table>
<%= strType.ToString()%></table>
</td>
<td style="width: 100px; height: 66px;"><table>
<%= strCount.ToString()%></table>
</td>
</tr>
</table>

[中国风]

2005用row_number

[中国风]

declare @T table([month] int,[Type] nvarchar(2),[id] int)
Insert @T
select 6,N'开户',2 union all
select 6,N'暂停',4 union all
select 6,N'注销',8 union all
select 7,N'开户',2 union all
select 7,N'暂停',2 union all
select 7,N'注销',2 union all
select 7,N'删除',2

;with C
as
(
Select *,row=row_number()over(partition by [month] order by [month]) from @T)
select
	[month]=case when row=1 then rtrim([month]) else '' end,
	[Type],
	[id]
from 
	C


(7 個資料列受到影響)
month        Type id
------------ ---- -----------
6            开户   2
             暂停   4
             注销   8
7            开户   2
             暂停   2
             注销   2
             删除   2

(7 個資料列受到影響)

[flairsky]

貌似不是distinct的问题

你一定要这么显示sql能做到，不过不如在程序里面做

[中国风]

確定有都有開戶時:
declare @T table([month] int,[Type] nvarchar(2),[id] int)
Insert @T
select 6,N'开户',2 union all
select 6,N'暂停',4 union all
select 6,N'注销',8 union all
select 7,N'开户',2 union all
select 7,N'暂停',2 union all
select 7,N'注销',2 union all
select 7,N'删除',2


select
	[month]=case when [Type]=N'开户' then rtrim([month]) else '' end,
	[Type],
	[id]
from 
	@T
month        Type id
------------ ---- -----------
6            开户   2
             暂停   4
             注销   8
7            开户   2
             暂停   2
             注销   2
             删除   2

(7 個資料列受到影響)

[mashixuan]

flairsky 看我的程序能给点意见吗 我改了代码好像没什么变化 谢谢
roy_88 谢谢 我是2005 但提示row_number不是可识别的函数

[叶子]

declare @hh table (id int ,mysell nvarchar(200))
insert into @hh
select 1,'asdfsadf' union 
select 3,'hfgfhfgh' union 
select 5,'sdfsa' union
select 7,'asdfsdfsa' union
select 8,'nhgnfg' 
SELECT row_number() 
OVER (ORDER BY id )AS Row,*
FROM @hh

row_number在我这一直都好使呀