需要有关sql查询的帮助。 [英] Need assistance with the sql query.

问题描述

我很难为这个问题找到一个可行的解决方案：我有一个有价值的表格

 name 值 
 A  0  
 A  10  
 A  20  
 B  0  
 B  50  
 B  70  
 B  100  

要求：i）找到每个名字的最小值和最大值，即

 name min max 
 A < span class =code-digit> 0   20  
 B  0  < span class =code-digit> 100  

然后将min和max分成10个相等的部分并显示如下：

 name range1 range2 
 A  0   2  
 A  2   4  
 A  4   6  
 A  6   8  
 A  8   10  
 A  10   12  
 A  12   14  
 A  14   16  
 A  16   18  
 A  18   20  

，类似于B.



我尝试过这样的事情：

 SELECT 
 name，
 MIN，
 MAX，
（MAX  -  MIN）/ 10 PARTITION_VALUE，
 FROM（SELECT 
 name，
 MIN（ value ）MIN，
 MAX（ value ）MAX 
 FROM [table] 
 GROUP BY name）A 

计算的最小值，最大值和将帮助的partition_value定义范围..即将10个不同部分中的最小值和最大值分开。

我无法继续进行。



谢谢

解决方案

HI这里是sql查询，你可以从中获得max&最小值名称

选择名称，（从表1中选择MIN（值）b其中b.name = a.name）为迷你，（从table1 c选择MAx（值），其中c.name = a.name）作为maxi从table1按名称分组

< br $> b $ b

谢谢。

hi Deepak，



谢谢你答案，但我想要的输出是别的。

我需要先找到名字的最小值和最大值（这是你查询的输出）。

之后基于这些值，我需要为每个名称创建10行，每个名称都有相同的分区。

就像A的最小值为0，最大值为20.

所以它的分区看起来像是

A 0

A 2

A 4

A 6

A 8

A 10

A 12

A 14

A 16

A 18

A 20



执行此操作后，我要求输出为：


A 0 2

A 2 4

A 4 6

A 6 8

A 8 10

A 10 12

A 12 14

A 14 16

A 16 18

A 18 20

  WITH  DATA_RANGE  AS （
  SELECT  NAME，DATA_VALUE，LEAD（DATA_VALUE） OVER （ PARTITION   BY 名称 ORDER   BY  DATA_VALUE）NEXT_VALUE  FROM  
（
  SELECT  NAME，MIN_VAL V1，MIN_VAL + PARTITION V2，MIN_VAL + PARTITION * 2 V3，MIN_VAL + PARTITION * 3 V4，MIN_VAL + PARTITION * 4 V5，MIN_VAL + PARTITION * 5 V6 
，MIN_VAL + PARTITION * 6 V7，MIN_VAL + PARTITION * 7 V8，MIN_VAL + PARTITION * 8 V9，MIN_VAL + PARTITION * 9 V10，MAX_VAL V11 
  FROM （
  SELECT  NAME，MIN_VAL，MAX_VAL，（MAX_VAL-MIN_VAL）/ 10  PARTITION  
  FROM （
  SELECT 名称，MAX（VALUE）MAX_VAL，MIN（VALUE）MIN_VAL  FROM  TAB 
  GROUP   BY  NAME）A）A）A 
 UNPIVOT 
（DATA_VALUE 
  FOR  DATA_TYPE  IN （V1，V2，V3，V4，V5，V6，V7，V8， V9，V10，V11））
  ORDER   BY  NAME，DATA_VALUE）
 < span class =code-keyword> SELECT  NAME，DATA_VALUE，NEXT_VALUE  FROM  DATA_RANGE 
  WHERE  NEXT_VALUE  IS   NOT   NULL  
  ORDER   BY  NAME ，DATA_VALUE 

I am having hard time thinking for a posible solution for this problem: i have one table that has value as

name    value
A        0
A        10
A        20
B        0
B        50
B        70
B        100

Requirement: i) find min and max value for each name i.e.

name min max
A     0   20
B     0   100

then divide the min and max to 10 equal parts and display like:

name range1 range2
A      0       2
A      2       4
A      4       6
A      6       8
A      8       10
A      10      12
A      12      14
A      14      16
A      16      18
A      18      20

and similarly for B.

I tried something like this:

SELECT
name,
MIN,
MAX,
(MAX - MIN)/10 PARTITION_VALUE,
FROM(SELECT
name,
MIN(value) MIN,
MAX(value) MAX
FROM [table]
GROUP BY name) A

calculated min value, max value and the partition_value that will help in defining the range.. i.e. dividing min and max value in 10 different parts.
I am just unable to proceed further.

Thanks

解决方案

HI here is the sql query from which you can get max & min values of names

select name, (select MIN(value) from table1 b where b.name=a.name) as mini,(select MAx(value) from table1 c where c.name=a.name) as maxi from table1 a group by name

thanks.

hi Deepak,

Thank you for the answer, but my desired output is something else.
I need to first find min and max values as per name (which is the output from your query).
After that based on those value, I need to create 10 rows with equal partitions for each name.
its like A has min as 0 and max as 20.
so its partition would look like
A 0
A 2
A 4
A 6
A 8
A 10
A 12
A 14
A 16
A 18
A 20

After doing this, i require the output as:

A 0 2
A 2 4
A 4 6
A 6 8
A 8 10
A 10 12
A 12 14
A 14 16
A 16 18
A 18 20

WITH DATA_RANGE AS(
SELECT NAME, DATA_VALUE, LEAD(DATA_VALUE) OVER (PARTITION BY NAME ORDER BY DATA_VALUE) NEXT_VALUE FROM
(
SELECT NAME,MIN_VAL V1,MIN_VAL+PARTITION V2,MIN_VAL+PARTITION*2 V3,MIN_VAL+PARTITION*3 V4,MIN_VAL+PARTITION*4 V5,MIN_VAL+PARTITION*5 V6
,MIN_VAL+PARTITION*6 V7,MIN_VAL+PARTITION*7 V8,MIN_VAL+PARTITION*8 V9,MIN_VAL+PARTITION*9 V10,MAX_VAL V11
FROM(
SELECT NAME,MIN_VAL,MAX_VAL,(MAX_VAL-MIN_VAL)/10 PARTITION
FROM(
SELECT NAME,MAX(VALUE) MAX_VAL, MIN(VALUE) MIN_VAL FROM TAB
GROUP BY NAME) A) A) A
UNPIVOT
(DATA_VALUE
FOR DATA_TYPE IN (V1,V2,V3,V4,V5,V6,V7,V8,V9,V10,V11))
ORDER BY NAME, DATA_VALUE)
SELECT NAME, DATA_VALUE, NEXT_VALUE FROM DATA_RANGE
WHERE NEXT_VALUE IS NOT NULL
ORDER BY NAME,DATA_VALUE

这篇关于需要有关sql查询的帮助。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！