表email_type中有以下字段和记录:
+----------------+--------------+--------------+
| email | domain_name | mailbox_type |
+----------------+--------------+--------------+
| [email protected] | 163.com | Free |
| [email protected] | 163.com | Free |
| [email protected] | 126.com | Free |
| [email protected] | 21cn.com | Pay |
| [email protected] | 21cn.com | Pay |
+----------------+--------------+--------------+
现在要求用SQL语句统计出如下效果(因上表中Free类的domain_name有3个,pay类的有2个,有两个163.com的domain_name...):
+--------------+--------------+--------------+--------------+
| mailbox_type | type_total | domain_name | domain_total |
+--------------+--------------+--------------+--------------+
| Free | 3 | 163.com | 2 |
| Free | 3 | 126.com | 1 |
| Pay | 2 | 21cn.com | 2 |
+--------------+--------------+--------------+--------------+请问SQL语句要怎么写?
+----------------+--------------+--------------+
| email | domain_name | mailbox_type |
+----------------+--------------+--------------+
| [email protected] | 163.com | Free |
| [email protected] | 163.com | Free |
| [email protected] | 126.com | Free |
| [email protected] | 21cn.com | Pay |
| [email protected] | 21cn.com | Pay |
+----------------+--------------+--------------+
现在要求用SQL语句统计出如下效果(因上表中Free类的domain_name有3个,pay类的有2个,有两个163.com的domain_name...):
+--------------+--------------+--------------+--------------+
| mailbox_type | type_total | domain_name | domain_total |
+--------------+--------------+--------------+--------------+
| Free | 3 | 163.com | 2 |
| Free | 3 | 126.com | 1 |
| Pay | 2 | 21cn.com | 2 |
+--------------+--------------+--------------+--------------+请问SQL语句要怎么写?
domain_name,count(*) domainCnt
from tb t
group by mailbox_type,domain_name
go
create table [email_type]([email] varchar(13),[domain_name] varchar(8),[mailbox_type] varchar(4))
insert [email_type]
select '[email protected]','163.com','Free' union all
select '[email protected]','163.com','Free' union all
select '[email protected]','126.com','Free' union all
select '[email protected]','21cn.com','Pay' union all
select '[email protected]','21cn.com','Pay'select
mailbox_type,
type_total=(select count(1) from email_type where mailbox_type=t.mailbox_type),
domain_name,
domain_total=(select count(1) from email_type where mailbox_type=t.mailbox_type and domain_name=t.domain_name)
from
[email_type] t
group by
mailbox_type,
domain_name
--测试结果:
/*
mailbox_type type_total domain_name domain_total
------------ ----------- ----------- ------------
Free 3 126.com 1
Free 3 163.com 2
Pay 2 21cn.com 2(所影响的行数为 3 行)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-08-06 17:31:54
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([email] varchar(13),[domain_name] varchar(8),[mailbox_type] varchar(4))
insert [tb]
select '[email protected]','163.com','Free' union all
select '[email protected]','163.com','Free' union all
select '[email protected]','126.com','Free' union all
select '[email protected]','21cn.com','Pay' union all
select '[email protected]','21cn.com','Pay'
--------------开始查询--------------------------
select
distinct a.mailbox_type,b.num as type_total ,a.domain_name ,c.num as domain_total
from
tb a,
(select mailbox_type,COUNT(1) as num from tb group by mailbox_type)b,
(select mailbox_type,domain_name,COUNT(1) as num from tb group by mailbox_type,domain_name)c
where
a.mailbox_type=b.mailbox_type
and
a.mailbox_type=c.mailbox_type
and
a.domain_name=c.domain_name
----------------结果----------------------------
/* mailbox_type type_total domain_name domain_total
------------ ----------- ----------- ------------
Free 3 126.com 1
Free 3 163.com 2
Pay 2 21cn.com 2(3 行受影响)
*/