一共是两个表,表project只有两列为id和project_name(项目名称),表Project_fj为其子表,以proje.id作为外键。表Project:USE [hw_sale]
GO
/****** 对象: Table [dbo].[project] 脚本日期: 08/11/2011 09:11:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[project](
[id] [int] IDENTITY(1,1) NOT NULL,
[project_name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[niaokantu] [image] NULL,
CONSTRAINT [PK_project] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
SET ANSI_PADDING OFF
表project_fj
USE [hw_sale]
GO
/****** 对象: Table [dbo].[project_fj] 脚本日期: 08/11/2011 09:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[project_fj](
[id] [int] IDENTITY(1,1) NOT NULL,
[bianhao] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ld_id] [int] NULL CONSTRAINT [DF_project_fj_ld_id] DEFAULT ((0)),
[danyuan] [int] NULL CONSTRAINT [DF_project_fj_danyuan] DEFAULT ((0)),
[louceng] [int] NULL CONSTRAINT [DF_project_fj_louceng] DEFAULT ((0)),
[chaoxiang] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_project_fj_chaoxiang] DEFAULT (''),
[hx_id] [int] NULL CONSTRAINT [DF_project_fj_hx_id] DEFAULT ((0)),
[mianji] [real] NULL CONSTRAINT [DF_project_fj_mianji] DEFAULT ((0)),
[danjia] [real] NULL CONSTRAINT [DF_project_fj_danjia] DEFAULT ((0)),
[zhuangtai] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_project_fj_zhuangtai] DEFAULT (''),
[project_id] [int] NULL CONSTRAINT [DF_project_fj_project_id] DEFAULT ((0)),
[wuyeleixing] [int] NULL CONSTRAINT [DF_project_fj_wuyeleixing] DEFAULT ((0)),
[bianhao_ccs] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_project_fj_id_ccs] DEFAULT ((0)),
[bianhao_ck] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_project_fj_id_ck] DEFAULT ((0)),
[mj_ccs] [real] NULL CONSTRAINT [DF_project_fj_mj_ccs] DEFAULT ((0)),
[mj_ck] [real] NULL CONSTRAINT [DF_project_fj_mj_ck] DEFAULT ((0)),
[jg_ccs] [real] NULL CONSTRAINT [DF_project_fj_jg_ccs] DEFAULT ((0)),
[jg_ck] [real] NULL CONSTRAINT [DF_project_fj_jg_ck] DEFAULT ((0)),
[fangwulaiyuan] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_project_fj] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GOSET ANSI_PADDING OFF
需求:根据project_id对表各项进行汇总计算各列总数,并汇总为一行。比如某项目的未售产品总数量总数,已售产品总数量总数。。
而我只能写出一列数据的汇总,不知道多列汇总该用哪种思路写,忘高手指教。我写的单列汇总:
select project_id, 'zongshu'=count(*) from project_fj GROUP BY project_id
GO
/****** 对象: Table [dbo].[project] 脚本日期: 08/11/2011 09:11:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[project](
[id] [int] IDENTITY(1,1) NOT NULL,
[project_name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[niaokantu] [image] NULL,
CONSTRAINT [PK_project] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
SET ANSI_PADDING OFF
表project_fj
USE [hw_sale]
GO
/****** 对象: Table [dbo].[project_fj] 脚本日期: 08/11/2011 09:11:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[project_fj](
[id] [int] IDENTITY(1,1) NOT NULL,
[bianhao] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ld_id] [int] NULL CONSTRAINT [DF_project_fj_ld_id] DEFAULT ((0)),
[danyuan] [int] NULL CONSTRAINT [DF_project_fj_danyuan] DEFAULT ((0)),
[louceng] [int] NULL CONSTRAINT [DF_project_fj_louceng] DEFAULT ((0)),
[chaoxiang] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_project_fj_chaoxiang] DEFAULT (''),
[hx_id] [int] NULL CONSTRAINT [DF_project_fj_hx_id] DEFAULT ((0)),
[mianji] [real] NULL CONSTRAINT [DF_project_fj_mianji] DEFAULT ((0)),
[danjia] [real] NULL CONSTRAINT [DF_project_fj_danjia] DEFAULT ((0)),
[zhuangtai] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_project_fj_zhuangtai] DEFAULT (''),
[project_id] [int] NULL CONSTRAINT [DF_project_fj_project_id] DEFAULT ((0)),
[wuyeleixing] [int] NULL CONSTRAINT [DF_project_fj_wuyeleixing] DEFAULT ((0)),
[bianhao_ccs] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_project_fj_id_ccs] DEFAULT ((0)),
[bianhao_ck] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_project_fj_id_ck] DEFAULT ((0)),
[mj_ccs] [real] NULL CONSTRAINT [DF_project_fj_mj_ccs] DEFAULT ((0)),
[mj_ck] [real] NULL CONSTRAINT [DF_project_fj_mj_ck] DEFAULT ((0)),
[jg_ccs] [real] NULL CONSTRAINT [DF_project_fj_jg_ccs] DEFAULT ((0)),
[jg_ck] [real] NULL CONSTRAINT [DF_project_fj_jg_ck] DEFAULT ((0)),
[fangwulaiyuan] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_project_fj] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GOSET ANSI_PADDING OFF
需求:根据project_id对表各项进行汇总计算各列总数,并汇总为一行。比如某项目的未售产品总数量总数,已售产品总数量总数。。
而我只能写出一列数据的汇总,不知道多列汇总该用哪种思路写,忘高手指教。我写的单列汇总:
select project_id, 'zongshu'=count(*) from project_fj GROUP BY project_id
,sum(未售产品数量) as 未售产品总数量
...
from project_fj GROUP BY project_id
字段自己找,我看不清
state project_id
未售 29
已售 30
未售 29
未售 29
,sum(case state when '未售' then 1 else 0 end) as 未售总数量
...
from project_fj GROUP BY project_id