Structured design for collection. There are many people who know that they can actually be used. To take a simple example: Example 1-3: There is a simple data sheet ORDERS, store orders for a store: Create Table [DBO]. [ORDERS] ([ID] [INT] Identity (1, 1) Not null [Customerid] [INT] NOT NULL, [ORDERDATE] [DATETIME] NOT NULL) ON [PRIMARY] GOCREATE CLUSTERED INDEX [CU_INX_ORDERDATE] ON [DBO]. [ORDERDATE]) with FillFactor = 50 on [primary] Goalter Table [DBO]. [Orders] with nocheck add constraint [pk_orders] Primary Key Nonclustered ([ID]) on [primary] Go table now has the following data: ID Customerid ORDERDATE ----------- ------------------------------------------------- --------------- 1 1 1999-1-42 10 1999-3-53 22 1999-5-24 2 1999-6-75 2 2000-3-67 101 2001-5 -38 10 2001-6-56 101 2002-4-2 So, how do we generate a 1999-2002 annual order number (only 8 orders for four years? I do this for demonstration convenience, this does not mean Real case: P)? Now, I give the data format of the actual report, please try the way of this statement Customerid 1999 2000 2001 2002 ------------------ ---- - ------ ------ 1 1 0 0 02 1 0 010 1 0 1 022 1 0 0101 0 0 1 1 The most intuitive idea is in the front desk, realizing this in other languages Features. However, there is a way to implement it with the SQL language. And not necessarily more complex than you imagine: Select Customerid, Sum (Case When Year (isnull (OrderDate, 0)) = 1999 Then 1 Else 0 End) AS "1999", SUM (Case When Year (isnull (ORDERDATE, 0 )) = 2000 THEN 1 ELSE 0 end) AS "2000", SUM (Case When Year (isnull (ORDERDATE, 0)) = 2001 THEN 1 ELSE 0 End) AS "2001", SUM (Case When Year (isnull (OrderDate , 0) = 2002 THEN 1 ELSE 0 END) AS "2002" from OrdersGroup by Customerid I want to have a friend to ask Interbase does not support Case. But even so, I still have to recommend this way of writing. Because it is beautiful, concise, not only we understand, but also easy to write procedures to automatically generate it. In fact, the CASE keyword is one of the SQL standards, and the general trend will have more and more database systems support it.
So what is it coming? I have this idea when designing this statement: 1. We need a report that started on both the two coordinate axes of time and customers; 2, vertical, we have to build a line of data for each customer, this is better First, we first determined this statement, there will be a basic framework Select Customerid, ................ from Ordersgroup By Customerid If not distinguishing the year, the next statement is our result Select Customerid, Count (ID) as Orders_count, from Ordersgroup By Customerid3, all orders are a complete set, then the total number of this collection is statistics with the following statement: Select Count (ID) from ORDERS horizontally, we define a column for each year's order, in 1999 as an example, take the year The number of elements for the 1999 order subsequence is SELECT SUM (Case When Year (isnull (OrderDate, 0)) = 1999 THEN 1 ELSE 0 END) AS "1999" from Orders Other Year of this year, we get every year Number of orders: Select SUM (isnull (OrderDate, 0)) = 1999 THEN 1 ELSE 0 End) AS "1999", SUM (Case When Year (isnull (ORDERDATE, 0)) = 2000 THEN 1 ELSE 0 END) AS "2000", SUM (Case When Year (isnull (OrderDate, 0)) = 2001 THEN 1 ELSE 0 END) AS "2001", SUM (isnull (orderdate, 0)) = 2002 THEN 1 ELSE 0 END) AS "2002" from Orders its return results are as follows: 1999 2000 2001 2002 ----------------------------- - ----------- 4 1 2 1
(The number of rows affected) 4, after consulting the relational database "Sprig" NULL problem, integrated 2, 3 steps, we got the final statement: Select Customerid, Sum (Case When Year (Isnull (ISNULL ORDERDATE, 0)) = 1999 THEN 1 ELSE 0 END) AS "1999", SUM (Case When Year (isnull (ORDERDATE, 0)) = 2000 THEN 1 ELSE 0 END) AS "2000", SUM (Case When Year) ISNULL (ORDERDATE, 0)) = 2001 THEN 1 ELSE 0 End) AS "2001", SUM (isnull (OrderDate, 0)) = 2002 THEN 1 ELSE 0 End) AS "2002" from OrdersGroup by CustomerId This report is clearly understood. Extensibility is extremely extended. For example, we take 2003 statistics next year, as long as you are alive, it is SUM (Case When Year (isnull (ORDERDATE, 0)) = 2003 THEN 1 ELSE 0 End) AS "2003" plus in the end. It is a subset of total 2003 data in full concentration. Also, the isnull function used to judge the null value is not necessarily all databases, it doesn't matter, just in the case's branch, one line of orde. is null kiloth 0 is OK. Based on this idea, we can easily write a stored procedure, just give a complete annual report, you can generate a complete annual report. Since all operations are running on the server side and is completed with the data retrieval. Its speed is faster than the client's report. Moreover, the amount of data transmitted is small, and the network load can be effectively reduced. In the "SQL Server 6.5 technology insider", there is a similar example. However, the author used by the author is more complicated than my complex. In his example, the from keyword is the data selected from a subquery exported to the table, which makes me ponder. Maybe the version 6.5 MS SQL Server does not support my writing, maybe it is better to write. The author did not explain that I have never had a chance to come into contact with MS SQL Server6.5. For InterBase, I haven't generated this report with a sufficiently elegant statement. This is mainly because Interbase does not support case.
However, if you are not high, the performance of the statement is not high, the following statement can achieve the same functionality as the above SQL Server version: Select O.Customerid, (Select Count (I.ID) from Orders Iwhere (i.customerid = O.CUSTOMERID) And (Year from i.orderdate) = 1999)) AS Count_1999, (Select Count (I.ID) from Orders Iwhere (i.customerid = O.CUSTOMERID) and (Extract (Year from I. ORDERDATE) = 2000)) AS Count_2000, (Select Count (I.ID) from Orders Iwhere (i.customerid = O.Customerid) and (Extract (Year from I.orderdate) = 2001)) AS Count_2001, (Select Count (SELECT COUNT I.ID) from Orders Iwhere (i.customerid = O.CUSTOMERID) And (Year from i.orderdate) = 2002)) AS Count_2002From ORDERS OGROUP BY O.CUSTOMERID In accordance with the SQL Server version, we completed the year of Interbase Report. Different is due to the use of related sub-query statistics, it will be different (I don't need instant update your annual report). However, since it is also based on a set-oriented design architecture, at least we guarantee its scalability. It is only obvious that when the sub-query version adds a list of annual statistics, the overhead growth will be much more than the Case version. If you have a higher speed, or write a program on the client. The user of the Interbase database will encounter a lot of dissatisfied places in this example: do not support automatic identity columns, no clustered indexes, no case, no ... more hateful, this data system open source version does not come with ODBC or ADO driver, after getting a free database system, we have to spend a $ 10 for dozens of dollars to buy an ODBC driver? However, Interbase is supporting the open source community, Borland also provides an open interface for InterBexPress and InterClient technology (current DBEXPRESS driver basically exists only in Borland's expensive enterprise development tool: () as long as each An Interbaser and user contributes to this belonging to our own software, it is still very bright. Although the design method is only applicable to specific goals, it is not a general software design method. But not In the three words, we will always actually have this design method, and we will have a special chapter to discuss this problem. At that time, our sample database was also constructed, and I might give more Practical annual order statistics report. Now let's take a brief summary: 1. Define the structure of the result set we have to generate; 2. Find the source of the result set, define the complete set; , Define the subset taken; 4, complete the operation.
