工单报表制作SQL语句,怎么查?

ak47zy ak47zy 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区
1.现有2个表:organizationunits(公司单位表) 、 workOrder(工单表)

2.我想制作报表,要查询出来的格式如下:

一级地区 二级地区 三级地区 工单数
惠普亚洲区 惠普中国区 惠普北京区 1

惠普亚洲区 惠普中国区 惠普上海区 1

惠普亚洲区 惠普韩国区 0

微软亚洲区 微软中国区 微软北京区 1

微软亚洲区 微软中国区 微软上海区 2

微软亚洲区 微软韩国区 0


以下是代码
/****** 对象:  Table [dbo].[organizationunits]    脚本日期: 10/17/2010 19:51:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[organizationunits](
[unitCode] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[unitName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[unitLevel] [int] NULL,
[parentCode] [varchar](6) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_organizationunits] PRIMARY KEY CLUSTERED
(
[unitCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单位编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'organizationunits', @level2type=N'COLUMN',@level2name=N'unitCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单位名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'organizationunits', @level2type=N'COLUMN',@level2name=N'unitName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单位级别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'organizationunits', @level2type=N'COLUMN',@level2name=N'unitLevel'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'父级单位编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'organizationunits', @level2type=N'COLUMN',@level2name=N'parentCode'


/****** 对象: Table [dbo].[workOrder] 脚本日期: 10/17/2010 19:52:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[workOrder](
[workCode] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[processUnit] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_workOrder] PRIMARY KEY CLUSTERED
(
[workCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工单编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'workOrder', @level2type=N'COLUMN',@level2name=N'workCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单位编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'workOrder', @level2type=N'COLUMN',@level2name=N'processUnit'


insert into organizationunits values ('010000','惠普亚洲区','1','')
insert into organizationunits values ('010100','惠普中国区','2','010000')
insert into organizationunits values ('010200','惠普韩国区','2','010000')
insert into organizationunits values ('010101','惠普北京区','3','010100')
insert into organizationunits values ('010102','惠普上海区','3','010100')

insert into organizationunits values ('020000','微软亚洲区','1','')
insert into organizationunits values ('020100','微软中国区','2','020000')
insert into organizationunits values ('020200','微软韩国区','2','020000')
insert into organizationunits values ('020101','微软北京区','3','020100')
insert into organizationunits values ('020102','微软上海区','3','020100')

insert into workOrder values ('2010101701','010101')
insert into workOrder values ('2010101702','010102')
insert into workOrder values ('2010101703','020101')
insert into workOrder values ('2010101704','020102')
insert into workOrder values ('2010101705','020102')




怎么做?
...全文

相关推荐

评论 4
收藏 已收藏
分享
返回顶部
ak47zy
疑难问题 22,299 成员 121,727 内容