How to create molecular queries from atoms in SQL Server

Each SQL Server developer has its own preferred method of action. My method is called molecular inquiry. These are queries that are grouped together by atomic queries, through which I can process a table. By combining atoms together, you can build molecules. Of course there will be limits (what chemists call the valence), but in general this principle still applies.

In this article, I'll explore several variations of this strategy. I start with the basics (that is, the most detailed content) and then step deeper. To give you an idea of the flexibility of this approach, I will use several techniques at different levels. (Warning: This is not the only solution, I'm just talking about some viable options.) ）

I started with the commonly used database Northwind (although I copied it to northwind_new in order to preserve the original, actually this is the database I used.) In my copy, I make these important changes.

I removed the composite primary key, added a new column called PK, and set it to the identity column.

I added a computed column called Extendedamount.

The following are the referenced contents:

Use [northwind_new]
Go
/****** object:table [dbo]. [Orderdetails_new]
Script date:08/23/2006 16:15:42 ******/
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
createtable [dbo]. [Orderdetails_new] (
[Orderdetailid] [INT] IDENTITY (1,1) Notnull,
[OrderID] [INT] Notnull,
[ProductID] [INT] Notnull,
[UnitPrice] [Money] Notnull,
[Quantity] [smallint] Notnull,
[Discount] [Real] Notnull,
[Extendedamount] As ([Quantity] * [UnitPrice] * (1-[Discount]),
CONSTRAINT [Pk_orderdetails_new] primarykeyclustered
(
[Orderdetailid] Asc
) on [PRIMARY]
) on [PRIMARY]

List A

List A contains a new (create) statement that creates the new table. I use this command to quickly transplant it and the value from the original table:

INSERT into [northwind_new]. [dbo]. [Orderdetails_new]

SELECT * FROM [northwind_new]. [dbo]. [Order Details]

Now that I have a multiple-line form, it's time to start experimenting. (Once the new column Extendedamount is computed, its value is automatically ported.) Initially, I wanted to use OrderID to calculate the total number of extendedamount columns. I set up a view, as shown in List B.

The following are the referenced contents:

Use [northwind_new]
Go
/****** Object:? View [dbo].
[Orderdetailssumbyorderid_vue] Script date:08/23/2006 16:31:18 ******/
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
CREATE VIEW [dbo]. [Orderdetailssumbyorderid_vue]
As
SELECT Top PERCENT OrderID,
SUM (Extendedamount) as TotalAmount
From?dbo. Orderdetails_new
GROUP by OrderID
ORDER BY OrderID

List B

I get a total number of Orderid,extendedamount columns per line. (called OrderAmount, add shipping, taxes, etc to get OrderTotal).