none
数据库查询 RRS feed

  • 问题

  • 大家好,刚开始学习SQL,从查询开始弄起,现在有一个问题挡住了,不知如何处理,请各位高手指点一二,谢谢!
    1,有2个表:
    表一:PrintData
    Id      PId            PrinterModel    PrinterId             TonerId        TotalPrint    InkLevel    Time
    5690285    10450042     M7218W        HP00093503HP06080838    1511MJ30001031     1705        30    2017/7/10 0:00
    5690286    10450043     LJ2208W       HP00058821HP0605        1605ML68004178     901          0    2017/7/10 0:00
    5690287    10450044     M7208W        HP00117725HP0702        1702ML68000816     203          0    2017/7/10 0:00
    5690288    10450045     M7208W        HP00149918HP0705        1705ML68006496     2            0    2017/7/10 0:00
    表二:PrintInfo
    Id                DeviceCode                                                        Platform        Version        Time           
    10450042    e83c4da6d5dcb11483150c21920617f72a302cfc                        iOS            0.2.9        2017/7/10 0:00   
    10450043    14:2D:27:46:5F:5F                                                WinPC         1.2.4.6      2017/7/10 0:00   
    10450044    60:45:CB:73:01:53                                                WinPC         1.2.4.6      2017/7/10 0:00   
    10450045    9C:D2:1E:84:B5:1D                                                WinPC         1.2.4.6      2017/7/10 0:00   
    10450051    8.64618E+14                                                      Android       0.2.4        2017/7/10 0:01   
    10450052    AC:E0:10:55:59:CE                                                WinPC         1.2.4.0      2017/7/10 0:02   
    10450053    8.6392E+14                                                       Android       0.2.3        2017/7/10 0:02   
    10450054    8.6392E+14                                                       Android       0.2.3        2017/7/10 0:02   
    10450057    6a14992b6b9a86f3e3a3d9bf1d5f213ebbc52c12                         iOS           0.2.9        2017/7/10 0:02   
    10450058    44:37:E6:35:0B:A7                                                WinPC         1.2.4.7        2017/7/10 0:02   
    其中,PrintData.PId与PrintInfo.Id 相互匹配,相互对应,也就是相同的值,数据库设置了权限,不能创建新表
    要查询的内容:
    1,PrintData中PrinterId是打印机的设备,找出PrinterId中在PrintInfo.Platform的对应情况,有如下几种情况
    a,PrinterId仅仅单独分别对应Android、iOS、WinPC的数量(3种)
    b,PrinterId对应WinPC +(Android 或iOS)的数量
    c,PrinterId对应Android +iOS的数量
    其中查找WinPC的情况时,要做特殊处理,要过滤PrintData.TotalPrint的重复项,即PrinterId 对应WinPC后,PrintData.TotalPrint数据发送变化一次才能算为1,如果PrintData.TotalPrint数据重复,不列入计数范围
    2017年7月17日 6:19

全部回复

  • Hi Deguang,

    对于你的需求我有几个疑问。 1. PrintId应该是可以被多个Platform使用的吧?也就是说PrintId会重复出现在PrintInfo表里面对吗?

    2. 你说的对WinPC做特殊处理,过滤TotalPrint的重复项是什么意思?怎么才能知道TotalPrint数据是不重复的?这个在你的描述里面没有清楚的提到。 请详细的解释下这部分的逻辑。

    3. 你已经分享了部分的示例数据,最好能根据这些示例数据贴一下你想要得到的结果。可以用Excel或者其他工具手动统计下。这样可以方面我们进行对比测试。

    我有根据你的需求写部分的query,请参考下,看是不是能满足你的需求。有问题,欢迎回馈:

    create table #PrintData
    (
    Id int,
    PId int,
    PrinterModel varchar(20),
    PrinterId varchar(50),
    TonerId varchar(50),      
    TotalPrint int,
    InkLevel int,   
    Time date
    )
    
    insert into #PrintData values(5690285,10450042,'M7218W','HP00093503HP06080838','1511MJ30001031',1705,30,'2017/7/10'),
    (5690286,10450043,'LJ2208W','HP00058821HP0605','1605ML68004178',901,0,'2017/7/10'),
    (5690287,10450044,'M7208W','HP00117725HP0702','1702ML68000816',203,0,'2017/7/10'),
    (5690288,10450045,'M7208W','HP00149918HP0705','1705ML68006496',2,0,'2017/7/10')
    
    create table #PrintInfo
    (
    Id int,
    DeviceCode varchar(255),
    PlatForm varchar(20),
    Version varchar(20),
    Time date
    )
    
    insert into #PrintInfo values
    (10450042,'e83c4da6d5dcb11483150c21920617f72a302cfc','iOS','0.2.9','2017/7/10'),
    (10450043,'14:2D:27:46:5F:5F','WinPC','1.2.4.6','2017/7/10'),   
    (10450044,'60:45:CB:73:01:53','WinPC','1.2.4.6','2017/7/10'),    
    (10450045,'9C:D2:1E:84:B5:1D','WinPC','1.2.4.6','2017/7/10'),    
    (10450051,'8.64618E+14','Android','0.2.4','2017/7/10'),    
    (10450052,'AC:E0:10:55:59:CE','WinPC','1.2.4.0','2017/7/10'),    
    (10450053,'8.6392E+14','Android','0.2.3','2017/7/10'),    
    (10450054,'8.6392E+14','Android','0.2.3','2017/7/10'),    
    (10450057,'6a14992b6b9a86f3e3a3d9bf1d5f213ebbc52c12','iOS','0.2.9','2017/7/10'),   
    (10450058,'44:37:E6:35:0B:A7','WinPC','1.2.4.7','2017/7/10') 
    
    select * from #PrintData
    select * from #PrintInfo
    
    select PId,PrinterId,pf.PlatForm,case when pf.PlatForm='Android' then count(PId) end as AndroidNum,
    case when pf.PlatForm='iOS' then count(PId) end as iOSNum,
    case when pf.PlatForm='WinPC' then count(PId) end as WinPCNum,
    case when pf.PlatForm in ('WinPC','Android') then count(PId) end as WinPC_AndroidNum,
    case when pf.PlatForm in ('WinPC','iOS') then count(PId) end as WinPC_iosNum,
    case when pf.PlatForm in ('Android','ios') then count(PId) end as Andriod_iosNum
    from #PrintData pd 
    join #PrintInfo pf on pd.PId=pf.Id
    group by PId,PrinterId,pf.PlatForm

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已建议为答案 Xi Jin 2017年7月24日 7:40
    2017年7月17日 8:31