如何从相关表MSSQL 2000中按类别获取项目数？

MODEL TABLE MODELID MODEL 1001 MODEL1 1002 MODEL2 现在，我需要生成一个如下所示的报告： 报告 MODELNAME初始进入转移平衡 MODEL1 2 2 0 2 MODEL2 2 2 1 1 为了进一步阐述，我需要在库存中显示特定模型的初始计数。然后计算模型被插入到由相关的HDR的DOCTYPE ='D'（借记）表示为ENTRY的数据库中的次数。此外，我还需要计算特定模型在其相关的HDR'S DOCTYPE AS'D'表示为TRANSFER的数据库中被标记为out的次数。并且，根据这些条目类型，计算数据库中的剩余余额（INITIAL - TRANSFER）。我尝试了各种方法，似乎逃脱了一个脚本，看起来像这样： 使用mss 声明@dtb as datetime 声明@dte为datetime select @dtb ='2016-01-01' select @dte ='2017-03-01' select m.modelid，m.model， --begin （从invdtl中选择计数（*）d 在d.docid上加入invhdr hdr = hdr.docid 其中d.modelid = m.modelid和d.apptype in（1,3）或（d.apptype = 7和hdr.doctype like'D'）和hdr.rcvdt @dtb和@dte ）as Beginning， --sales （ select count（*）from invdtl d join invhdr hdr on d.docid = hdr.docid 其中d.modelid = m.modelid和d.apptype = 2 和hdr.docdt之间的@dtb和@dte ）作为销售额， - delivery （ select count（*）from invdtl d join invhdr hdr on d.docid = hdr.docid 其中d.modelid = m.modelid和hdr.apptype = @dtb和@dte 之间1 和hdr.rcvdt）作为交货， - MT （从invdtl选择计数（*）d 加入invdr hdr在d.docid = hdr.docid 其中d.modelid = m.modelid和hdr.apptype = 5 和hdr.rcvdt在@dtb和@dte 之间作为转移， - 服务单位（ select count（*）from invdtl d join invhdr hdr on d.docid = hdr.docid 其中d.modelid = m.modelid和hdr.apptype = 6 和hdr.docdt在@dtb和@dte 之间作为[服务单位]， --repossesed （从invdtl选择计数（*） d 在d.docid = hdr.docid 上加入invhdr hdr，其中d.modelid = m.modelid和hdr.apptype = 3 和hdr.docdt介于@dtb和@dte $ b之间$ b）作为交付， --adj （从invdtl选择计数（*）d 加入invdr hdr在d.docid = hdr.docid 其中d.modelid = m.modelid和hdr.apptype = 7而hdr.doctype ='D'和hdr.rcvdt在@dtb和@dte 之间）为[ADJ-DB] ， --adj （从invdtl中选择计数（*）d 在d.docid = hdr.docid 上加入invhdr hdr，其中d.modelid = m.modelid和hdr.apptype = 7，hdr.doctype ='C'和hdr.rcvdt在@dtb和@之间dte ）as [ADJ-CR] 来自型号m --join 其中 m.modelid in（从invdtl d中选择不同的d.modelid）由m.model命令asc 从invdtl中选择*其中modelid = 519 ，它在虚拟数据库上提供了所需的结果，我设置了200条记录。但是当我在具有超过100k记录的实际测试数据库上尝试它时，需要永远加载数据。有关如何实现相同输出的任何提示，而不必为我尝试的脚本的结果花费那么长时间？非常感谢您的帮助:) 我的尝试： 尝试了类似这样的方法： SELECT 类别， COUNT（*）AS 'num' 来自 帖子 GROUP BY 类别 但很难将我的脚本修改为相同的方法。解决方案 尝试这样的事情： USE mss; DECLARE @ dtb as datetime = ' 20160101'; DECLARE @ dte as datetime = ' 20170301'; WITH cteStats As （ SELECT d.modelid， SUM（ CASE WHEN hdr.apptype 在（ 1 ， 3 ）那么 1 WHEN hdr.apptype = 7 和 hdr.doctype 喜欢 ' D' 那么 1 ELSE 0 END ） As Beg局， SUM（ CASE WHEN hdr.apptype = 2 那么 1 ELSE 0 END ） As Sales， SUM（ CASE WHEN hdr.apptype = 1 那么 1 ELSE 0 END ）作为交付， SUM（ CASE WHEN hdr.apptype = 5 那么 1 ELSE 0 END ） As 转移， SUM（ CASE WHEN hdr.apptype = 6 那么 1 ELSE 0 END ）作为 [服务单位]， SUM（ CASE WHEN hdr.apptype = 3 那么 1 ELSE 0 END ） As Repossessed， SUM（ CASE WHEN hdr.apptype = 7 和 hdr.doctype = ' D' 那么 1 ELSE 0 END ）作为 [ADJ-DB]， SUM（ CASE WHEN hdr.apptype = 7 和 hdr.doctype = ' C' 那么 1 ELSE 0 END ）作为 [ADJ-CR] FROM dbo.invdtl 作为 d INNER JOIN dbo.invhdr 作为 hdr ON hdr.docid = d.docid WHERE hdr.rcvdr BETWEEN @ dtb 和 @ dte GROUP BY d.modelid ） SELECT m.modelid， m.model， s.Beginning， s.Sales， s.Delivery， s.Transfer， s。[Service Unit]， s.Repossessed， s。[ADJ-DB] ， s。[ADJ-CR] FROM dbo.model As m INNER JOIN cteStats As s ON s.modelid = m.modelid ORDER BY m.model ASC ; Hello everyone. I am currently developing a simple inventory system and Im kinda new to TSQL. And here's my problem: I need some help with in my hope u can provide me with some assistance. I have three tables named HDR, DTL and MODEL. They contain sample data as illustrated below : HDR TABLE DOCIDDOCNODOCTYPEAPPTYPE 0001ABCDDC1000 0002ABCDEC1000 0003ABCDFD1001 DTL TABLE DTLIDDOCIDMODELIDENGINE NO. 1000110011111 2000110011112 3000210021113 4000210021114 5000310021114 MODEL TABLE MODELIDMODEL 1001MODEL1 1002MODEL2Now, I need to generate a report looking like this: REPORT MODELNAMEINITIALENTRYTRANSFERBALANCE MODEL12202 MODEL22211To further expound, I need to show the initial count of a particular model on the inventory. Then count the number of times a model has been "inserted" into the database denoted as "ENTRY" by its related HDR's DOCTYPE = 'D' (Debit). Further, I need also to count the number of times the particular model has been marked as "out" in the database denoted as "TRANSFER" by its related HDR'S DOCTYPE AS 'D'. And, based on those entry types, count the remaining balance (INITIAL - TRANSFER) in the database. I have tried various approaches and seem to get away with a script which somehow looks like this:use mssdeclare @dtb as datetimedeclare @dte as datetimeselect @dtb = '2016-01-01'select @dte = '2017-03-01'select m.modelid, m.model, --begin(select count(*) from invdtl d join invhdr hdr on d.docid = hdr.docidwhere d.modelid = m.modelid and d.apptype in (1,3) or (d.apptype = 7 and hdr.doctype like 'D')and hdr.rcvdt between @dtb and @dte ) as Beginning,--sales(select count(*) from invdtl d join invhdr hdr on d.docid = hdr.docidwhere d.modelid = m.modelid and d.apptype = 2and hdr.docdt between @dtb and @dte ) as Sales,--delivery(select count(*) from invdtl d join invhdr hdr on d.docid = hdr.docidwhere d.modelid = m.modelid and hdr.apptype = 1and hdr.rcvdt between @dtb and @dte ) as Delivery,--MT(select count(*) from invdtl d join invhdr hdr on d.docid = hdr.docidwhere d.modelid = m.modelid and hdr.apptype = 5and hdr.rcvdt between @dtb and @dte ) as Transfer,--service unit(select count(*) from invdtl d join invhdr hdr on d.docid = hdr.docidwhere d.modelid = m.modelid and hdr.apptype = 6and hdr.docdt between @dtb and @dte ) as [Service Unit],--repossesed(select count(*) from invdtl d join invhdr hdr on d.docid = hdr.docidwhere d.modelid = m.modelid and hdr.apptype = 3and hdr.docdt between @dtb and @dte ) as Delivery,--adj(select count(*) from invdtl d join invhdr hdr on d.docid = hdr.docidwhere d.modelid = m.modelid and hdr.apptype = 7 and hdr.doctype = 'D'and hdr.rcvdt between @dtb and @dte ) as [ADJ-DB],--adj(select count(*) from invdtl d join invhdr hdr on d.docid = hdr.docidwhere d.modelid = m.modelid and hdr.apptype = 7 and hdr.doctype = 'C'and hdr.rcvdt between @dtb and @dte ) as [ADJ-CR]from model m--joinwhere m.modelid in (select distinct d.modelid from invdtl d) order by m.model ascselect * from invdtl where modelid = 519which gives a the desired result on a dummy database I have set up with a meager 200 records. But when I tried it on my actual test database with more than 100k records, it takes forever to load the data. Any tips on how to achieve the same output without having to ait that long for the result of the script I tried? Your help would be much appreciated :)What I have tried:tried something of an approach like this :SELECT category, COUNT(*) AS 'num'FROM postsGROUP BY categorybut had a hard time modifying my script into the same approach. 解决方案 Try something like this:USE mss;DECLARE @dtb as datetime = '20160101';DECLARE @dte as datetime = '20170301';WITH cteStats As( SELECT d.modelid, SUM(CASE WHEN hdr.apptype In (1, 3) THEN 1 WHEN hdr.apptype = 7 And hdr.doctype like 'D' THEN 1 ELSE 0 END) As Beginning, SUM(CASE WHEN hdr.apptype = 2 THEN 1 ELSE 0 END) As Sales, SUM(CASE WHEN hdr.apptype = 1 THEN 1 ELSE 0 END) As Delivery, SUM(CASE WHEN hdr.apptype = 5 THEN 1 ELSE 0 END) As Transfer, SUM(CASE WHEN hdr.apptype = 6 THEN 1 ELSE 0 END) As [Service Unit], SUM(CASE WHEN hdr.apptype = 3 THEN 1 ELSE 0 END) As Repossessed, SUM(CASE WHEN hdr.apptype = 7 and hdr.doctype = 'D' THEN 1 ELSE 0 END) As [ADJ-DB], SUM(CASE WHEN hdr.apptype = 7 and hdr.doctype = 'C' THEN 1 ELSE 0 END) As [ADJ-CR] FROM dbo.invdtl As d INNER JOIN dbo.invhdr As hdr ON hdr.docid = d.docid WHERE hdr.rcvdr BETWEEN @dtb And @dte GROUP BY d.modelid)SELECT m.modelid, m.model, s.Beginning, s.Sales, s.Delivery, s.Transfer, s.[Service Unit], s.Repossessed, s.[ADJ-DB], s.[ADJ-CR]FROM dbo.model As m INNER JOIN cteStats As s ON s.modelid = m.modelidORDER BY m.model ASC; 这篇关于如何从相关表MSSQL 2000中按类别获取项目数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！