Sales _ Regional Sales Partition Table
Primary key # region _id
class_id
Gong Hao
Prod_class product classification table
Primary Key # Classification ID
Category name
Question sql statement:
choose
obvious
pa.class_id
from
Product _ category
be like
Pa, production class
be like
Pb, sales area
where
pb.class_id
In (select)
Sales area. Category identification
from
Sales _ region
where
sale _ area.gonghao = & ltparam & gt)
and
pa.class_id
like
pb.class_id||'% '
Problem definition:
According to the passed-in parameter Gong Hao, the corresponding class_id set is determined, and then the class_id set is traversed and "expanded".
To understand "extension", you must know that class_id is graded like this:
0 is the highest level.
|_0 1
|
|_0 1 1
|
|_0 12
|_02
|_0 1 1
|_0 12
Then if you have level 0, all levels should be accessible. If you have grades 0 1, 0 1 1 and 0 12, you can also visit them.
The purpose of this sql is to obtain all product categories and their subsets that current employees can access.
Self-join is correct in Oracle, but wrong in DB2, because at least one side of the predicate in DB2 is a string, which means that DB2 does not think Pb. Class _ id |||'%' is a string.
Solve:
If there are any alternatives or improvements to accomplish the above tasks, please note that the environment is DB2.
UDB
7.2。
-
It's that tree.
This can be achieved by using common * * * expressions.
Press recusion in the information center to find out how to write recursive sql.
-
The question is clearly written, I like it!
If you want to use the male * * * expression of DB2, I suggest that you set the.
Change the structure of product classification table
Primary Key # Classification ID
Category name
Parent_Class_id
The sql statement is as follows:
along with
Temporary category identification (
Category identification, category name)
(
choose
pc.class_id，pc.class_name
from
Product _ category
personal computer
where
pc.class_id
exist
(select
Sales area. Category identification
from
Sales _ region
where
sale _ area.gonghao = & ltparam & gt)
alliance
choose
pc.class_id，pc.class_name
from
Product _ category
pc，
Temporary category identification
Turks and Caicos Islands
where
pc.parent_id
=
tc.class_id)
choose
*
from
Temporary class identification;