如何写sql语句，给个思路也行，记录表有几千万条记录，产品表有几百万条，人员表有几百条

Question

记录表：

 CREATE TABLE [dbo].[wsd_count](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [uid] [varchar](64) NULL,
    [line] [varchar](64) NULL,
    [num] [varchar](64) NULL,
    [result] [varchar](64) NULL,
    [data] [datetime] NULL,
    [count_reserve1] [nvarchar](255) NULL,
    [count_reserve2] [nvarchar](255) NULL,
    [count_reserve3] [nvarchar](255) NULL,
    [count_reserve4] [nvarchar](255) NULL,
    [count_reserve5] [datetime] NULL,
 CONSTRAINT [PK_wsd_count] 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]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'uid'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产线' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'line'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'num'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结果' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'result'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'data'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_count', @level2type=N'COLUMN',@level2name=N'count_reserve5'
GO

产品表：

 CREATE TABLE [dbo].[wsd_product](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [num] [varchar](64) NULL,
    [pmname] [varchar](64) NULL,
    [prname] [varchar](64) NULL,
    [scdata] [datetime] NULL,
    [zsdata] [datetime] NULL,
    [type] [varchar](64) NULL,
    [color] [varchar](64) NULL,
    [ptlot] [varchar](64) NULL,
    [car] [varchar](64) NULL,
    [remake] [varchar](64) NULL,
    [product_reserve1] [nvarchar](255) NULL,
    [product_reserve2] [nvarchar](255) NULL,
    [product_reserve3] [nvarchar](255) NULL,
    [product_reserve4] [nvarchar](255) NULL,
    [product_reserve5] [nvarchar](255) NULL,
    [product_reserve6] [nvarchar](255) NULL,
    [product_reserve7] [nvarchar](255) NULL,
    [product_reserve8] [datetime] NULL,
    [product_reserve9] [datetime] NULL,
    [product_reserve0] [datetime] NULL,
 CONSTRAINT [PK_wsd_product] 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]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'件号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'num'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'pmname'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'prname'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'生产日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'scdata'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注塑日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'zsdata'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品分类' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'type'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'颜色' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'color'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'喷涂批次' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'ptlot'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'车号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'car'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'remake'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve3'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve4'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve5'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve6'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段7' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve7'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段8' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve8'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段9' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve9'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'预留字段0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wsd_product', @level2type=N'COLUMN',@level2name=N'product_reserve0'
GO

人员表：

 CREATE TABLE [dbo].[sys_user](
    [id] [varchar](64) NOT NULL,
    [company_id] [varchar](64) NOT NULL,
    [office_id] [varchar](64) NOT NULL,
    [login_name] [varchar](100) NOT NULL,
    [password] [varchar](100) NOT NULL,
    [no] [varchar](100) NULL,
    [name] [varchar](100) NOT NULL,
    [email] [varchar](200) NULL,
    [phone] [varchar](200) NULL,
    [mobile] [varchar](200) NULL,
    [user_type] [char](1) NULL,
    [photo] [varchar](1000) NULL,
    [login_ip] [varchar](100) NULL,
    [login_date] [datetime] NULL,
    [login_flag] [varchar](64) NULL,
    [create_by] [varchar](64) NOT NULL,
    [create_date] [datetime] NOT NULL,
    [update_by] [varchar](64) NOT NULL,
    [update_date] [datetime] NOT NULL,
    [remarks] [varchar](255) NULL,
    [del_flag] [char](1) NOT NULL,
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]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[sys_user] ADD  DEFAULT ('0') FOR [del_flag]
GO

背景：某零件加工厂有几条产线检测产品是否合格，每检测一件产品在记录表中添加一条记录，检测结果放到result字段中，检测结果有四个大类，三十多小类，详情如下：

合格抛光打磨报废
打磨流挂
打磨颗粒。。。
要求：
统计某个员工某个时间段做的某个产品的统计信息，包括员工个人信息，一次合格率（合格/总数），二次合格率（（合格+抛光*0.85）/所有），各个检测结果的数量。
查询条件为空时列出所有员工，所做的所有产品的统计记录，每个产品单独占一行。

使用sqlsever2008，查询百万数据速度在三秒内可增加悬赏。

Answer 1

用临时表代替游标，你试试。
不过3秒不行，你这判断条件基本都是走全表的。
你可以逐条看看哪一块慢，进行针对优化 。
而且你可以看看是否有哪些是无用的。
你给各个关键点加索引。

Answer 2

你需要实时查询还是一段时间查一次？

Answer 3

百万数据3秒查询，这有点费劲吧，而且还需要关联表，给表建索引是必不可少的，调用存储过程

Answer 4

存储过程实现：

```ALTER PROCEDURE [dbo].[wsdmansum]
-- Add the parameters for the stored procedure here
@gh nvarchar(255), @xm nvarchar(255), @cx varchar(64),
@cp varchar(64), @jg varchar(64), @sdata datetime, @edata datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @moth varchar(64),@weekday varchar(64),@scdata datetime, @zsdata datetime, @jcdata datetime;
declare @num varchar(64), @pmname varchar(64), @prname varchar(64);
declare @prtype varchar(64), @color varchar(64), @ptpc varchar(64);
declare @car varchar(64), @remakes varchar(64), @ychgl float, @echgl float;
declare @hg int, @pg int, @dm int, @bf int;
declare @id varchar(64), @uid varchar(64), @value VARCHAR(10);
-- Insert statements for procedure here
--声明变量，存放当前已开启的事务数
declare @exist_trancount int
select @exist_trancount = @@trancount

if @exist_trancount > 0
    --创建事务保存点
    save transaction tran_proc
else
    --开启新事务
    begin transaction tran_proc


-- 定义临时表
if object_id('tempdb..#mypsum') is not null drop table #mypsum
create table #mypsum(
        id int,pid varchar(64),moth varchar(64),weekday varchar(64),scdata datetime,zsdata datetime,jcdata datetime,
        num varchar(64),pmname varchar(64),prname varchar(64),prtype varchar(64),color varchar(64),ptpc varchar(64),car varchar(64),remakes varchar(64),
        ychgl float,echgl float,hg int,pg int,dm int,bf int
        )


-- 定义游标.
DECLARE c_test_main CURSOR FAST_FORWARD FOR
SELECT distinct uid FROM wsd_count a where 
a.count_reserve1 like case when @gh is not null and @gh!='' then @gh+'%' else a.count_reserve1 end 
and a.count_reserve2 like case when @xm is not null and @xm!='' then @xm+'%' else a.count_reserve2 end 
and a.line like case when @cx is not null and @cx!='' then @cx+'%' else a.line end 
and a.num like case when @cp is not null and @cp!='' then @cp+'%' else a.num end
and a.data >= case when @sdata is not null and @sdata!='' then @sdata else a.data end
and a.data <= case when @edata is not null and @edata!='' then @edata else a.data end;
  -- 打开游标.
OPEN c_test_main;

WHILE 1=1
  BEGIN
    -- 填充数据.
    FETCH NEXT FROM c_test_main INTO @uid;
    -- 假如未检索到数据，退出循环.
    IF @@fetch_status!= 0 BREAK;

        -- 定义游标.
        DECLARE c_cp_main CURSOR FAST_FORWARD FOR
        SELECT distinct num FROM wsd_count a where a.uid=@uid 
        and a.line like case when @cx is not null and @cx!='' then @cx+'%' else a.line end 
        and a.num like case when @cp is not null and @cp!='' then @cp+'%' else a.num end
        and a.data >= case when @sdata is not null and @sdata!='' then @sdata else a.data end
        and a.data <= case when @edata is not null and @edata!='' then @edata else a.data end;
          -- 打开游标.
        OPEN c_cp_main;

        WHILE 1=1
          BEGIN
            -- 填充数据.
            FETCH NEXT FROM c_cp_main INTO @id;
            -- 假如未检索到数据，退出循环.
            IF @@fetch_status!= 0 BREAK;

                SELECT
                    @moth = (select distinct a.count_reserve1 from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @weekday = (select distinct a.count_reserve2 from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @scdata = (select distinct b.scdata from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @zsdata = (select distinct b.zsdata from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @num = (select distinct b.num from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @pmname = (select distinct b.pmname from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @prname = (select distinct b.prname from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @prtype = (select distinct b.type from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @color = (select distinct b.color from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @ptpc = (select distinct b.ptlot from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @car = (select distinct b.car from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @remakes = (select distinct b.remake from wsd_count a,wsd_product b where a.num=b.id and a.num=@id and a.uid=@uid),
                    @hg = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '合格%'),
                    @pg = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '抛光%'),
                    @dm = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '打磨%'),
                    @bf = (select count(a.id) from wsd_count a where a.num=@id and a.uid=@uid and a.result LIKE '报废%');
                    if (@hg+@pg+@dm+@bf)>0
                        begin
                            select @echgl = (@hg+@pg*0.85)/(@hg+@pg+@dm+@bf) , @ychgl = cast(@hg as float)/(@hg+@pg+@dm+@bf) ,
                             @ychgl =  Convert(decimal(20,4),@ychgl) , @echgl = Convert(decimal(20,4),@echgl);
                        end
                        else
                        begin
                            select @ychgl = 0,@echgl = 0;
                        end

                    INSERT INTO #mypsum ([pid],[moth],[weekday],[scdata],[zsdata],[jcdata],[num],[pmname],[prname],[prtype],[color],[ptpc],[car],[remakes],[ychgl],[echgl],[hg],[pg],[dm],[bf])
                    VALUES
                        (@id,@moth,@weekday ,@scdata,@zsdata,@jcdata,@num,@pmname,@prname,@prtype,@color,@ptpc,@car,@remakes,@ychgl,@echgl,@hg,@pg,@dm,@bf)

            END;
            -- 关闭游标
          CLOSE c_cp_main;
          -- 释放游标.
          DEALLOCATE c_cp_main;

  END;

  -- 关闭游标
  CLOSE c_test_main;
  -- 释放游标.
  DEALLOCATE c_test_main;

  select * from #mypsum;

  drop table #mypsum;

if @@error<>0
goto error

if @exist_trancount = 0
    --提交事务
    commit tran tran_proc
    return 1
error:
    --回滚事务或者事务保存点
    rollback transaction tran_proc
    return -1

END

Answer 5

这么说吧，数据量如果达到千万级以上的，实时查询的速度就会降下来，因此你要求的快速查询，就不能进行实时查询，数据体量过大的情况下
建议你使用spark进行计算，然后再去取数据吧。

Answer 6

分表，建热点数据。 或者查询结果放缓存。