Shulou(Shulou.com)06/01 Report--

Today, I'll show you how SQL queries the field values that appear in each group. The content of the article is good. Now I would like to share it with you. Friends who feel in need can understand it. I hope it will be helpful to you. Let's read it along with the editor's ideas.

After the data is grouped, you need to find a field value that appears in each group.

It is not easy to solve this problem with SQL!

SQL lacks simple grouping operation, so it can only return the statistical values of each group, but can not perform more detailed set operations such as intersection, difference, union and so on. To solve this problem, multi-layer nested subqueries are needed to implement in disguise.

For example: ask employees who work overtime every week for recognition. The existing overtime data sheet OVERTIME_WORK data are as follows:

WORKDATENAME2019-07-01Tom2019-07-02John2019-07-03Tom2019-07-04Cart2019-07-08Tom2019-07-09Jordan2019-07-10Kate2019-07-10John2019-07-15Leon2019-07-16John2019-07-17Tom2019-07-18John2019-07-22Jim2019-07-23Tom2019-07-24John2019-07-25Cart.

Take Oracle, for example, and write it in SQL as follows:

WITH An AS

(SELECT COUNT (*) NUM FROM

(SELECT DISTINCT TO_CHAR (WORKDATE,'IW') FROM OVERTIME_WORK)

),

B AS

(SELECT TO_CHAR (WORKDATE,'IW') W, NAME

FROM OVERTIME_WORK

GROUP BY TO_CHAR (WORKDATE,'IW'), NAME

),

C AS

(SELECT NAME, COUNT (*) NUM FROM B GROUP BY NAME)

SELECT NAME FROM C WHERE NUM= (SELECT NUM FROM A)

Here A finds out a total of several weeks, B finds out different employees who work overtime each week, C finds out the number of appearances of employees in B, and finally selects employees from C who appear as many times as the total number of weeks. The solution is rather roundabout, this kind of SQL is both difficult to write and difficult to understand.

The SPLlanguage of the aggregator supports set operation, and it is much easier to solve this problem. It only takes one line of code to calculate the intersection of employees who work overtime every week:

Connect ("mydb") .query ("SELECT * FROM OVERTIME_WORK") .group ((WORKDATE-date ("2019-07-01"))\ 7). (~. (NAME)) .isect ()

This is how SQL queries all the field values that appear in each group. for more information related to how SQL queries the field values that appear in each group, you can search for previous articles or browse the following articles to learn! I believe the editor will add more knowledge to you. I hope you can support it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.