Arsip: Bewara SQL for maniak
more 16 years ago
ImanD
rekan-rekan maniak sql, bisakan kalian pecahkan masalah ini....!
tproduct
ID_product Qty
========== ===
CD0001 3
CD0002 1
CD0003 5
CD0004 2
CD0005 7
CD0006 4
tproductitemstoct
ID_product item choice
========== ==== ======
CD0001 1 Y
CD0001 2 Y
CD0001 3 Y
CD0002 1 Y
CD0003 1 Y
CD0003 2 N
CD0003 3 Y
CD0003 4 N
CD0003 5 Y
CD0004 1 N
CD0004 2 Y
CD0005 1 N
CD0005 2 N
CD0005 3 N
CD0005 4 N
CD0005 5 N
CD0005 6 N
CD0005 7 N
CD0006 1 N
CD0006 2 N
CD0006 3 N
CD0006 4 N
HASIL QUERY
ID_product qty choice
========== === ======
CD0001 3 111
CD0002 1 1
CD0003 5 10101
CD0004 2 01
notes:
Choice: Y=1
N=0
klo item pada suatu ID_product ga ada yg pilih(N semua) maka ga di tampilin
Full SQL is right
more 16 years ago
reminder2k1
Ayo temen2 yang bisa, tlg bagi ilmunya... Aku dah nyerah deh... :mrgreen:
more 16 years ago
Manz
lha ... koq edited seh .... yang pertama ajah sqlnya belon nemu ... sekarang dah diganti ke inginan.... wakakaakakakakakak :mrgreen:
more 16 years ago
ImanD
@manz_delphi
xixiixixi, tinggal ngesplit........wakakakakka aku malah bingung mo split dari data dari hasilmu.
oia u/ yg edited itu aku lupa mo nyertakan semua data, biar lebih rameeeeeeeeeee!
more 16 years ago
cyber_hecker
wakakaka.... moso harus diriku yg turun tangan nih ?? :D
set @cho1 = '1', @cho2 = '1', @grup = '', @urut = 1;
SELECT id_product, MAX(cast(xgrup as char)) as y
FROM (
SELECT id_product, choice,
@cho2 := CASE choice WHEN 'Y' THEN 1 ELSE 0 END AS xchoi,
CASE WHEN @grup = id_product THEN
@cho1 := CONCAT(@cho1,@cho2)
ELSE
@cho1 := @cho2
END AS xgrup,
@grup := id_product
FROM iman_stock
WHERE id_product IN
(
SELECT id_product
FROM iman_stock
WHERE choice = 'Y'
GROUP BY id_product
)
ORDER BY id_product
) Q1
group by id_product
nah nanti hasilnya adalah sebagai berikut :
id_product y
CD0001 111
CD0002 1
CD0003 10101
CD0004 01
SELECT id_product
FROM iman_stock
WHERE choice = 'Y'
GROUP BY id_product
untuk menampilkan data yang tedapat nilai Y, jika item gak ada nilai Y ya udah lewaaaaat...
SELECT id_product, choice,
@cho2 := CASE choice WHEN 'Y' THEN 1 ELSE 0 END AS xchoi,
CASE WHEN @grup = id_product THEN
@cho1 := CONCAT(@cho1,@cho2)
ELSE
@cho1 := @cho2
END AS xgrup,
@grup := id_product
FROM iman_stock
WHERE id_product IN
(
SELECT id_product
FROM iman_stock
WHERE choice = 'Y'
GROUP BY id_product
)
ORDER BY id_product
harus di order berdasarkan id_produk, kalo enggak bisa berantakan proses pemberian tanda 1 dan 0 nya :D
trus xchoi itu untuk menyimpan nilai 1 atau 0 sementara.
case ... ini berfungsi untuk memeriksa, kodenya masih sama gak ama sebelumnya. jika gak sama berarti data baru.. reset kembali 1 / 0 nya. jika masih sama berarti lanjutkan CONCAT datanya.
wekekekek.. di tunggu nih yg lebih sulit.. moso cuma segini. huakakakak :P
more 16 years ago
ImanD
nah lho, berapa query nih..... di join az bro.......
bisa kan 1query, tanpa SP atw function or apalah itu, xixixiix
more 16 years ago
_lmz
Dengan postgresql (tested on v7.4):
[code:1:65bcab959e]
CREATE FUNCTION concatenate(text,text) RETURNS text AS
'SELECT $1 || $2;' LANGUAGE SQL;
CREATE AGGREGATE group_concat(
BASETYPE=text,
SFUNC=concatenate,
STYPE=text,
INITCOND=''
);
select tproduct.id_product, tproduct.qty, grouped_products.choice
from
(select id_product, group_concat(case choice when 'Y' then '1' else '0' end) as
from tproductitemstoct
group by id_product
having position('1' in group_concat(case choice when 'Y' then '1' else '0' end)) <> 0) as grouped_product
inner join tproduct
on (grouped_products.id_product = tproduct.id_product);
[/code:1:65bcab959e]
hasil:
CREATE FUNCTION
CREATE AGGREGATE
id_product | qty | choice
------------+-----+--------
CD0001 | 3 | 111
CD0002 | 1 | 1
CD0003 | 5 | 10101
CD0004 | 2 | 01
(4 rows)
more 16 years ago
reminder2k1
Coba spt ini bro:
SELECT L2.ID_product, COUNT(L2.item) as qty,
SUM(L2.Nilai) AS choice
FROM (SELECT L1.ID_product, L1.item, L1.choice,
CASE L1.choice
WHEN 1 THEN POWER(10,L1.NilaiMax-L1.item)
ELSE 0 END AS Nilai
FROM (SELECT L.ID_product, L.item, L.choice,
(SELECT MAX(item)
FROM tproductitemstoct
WHERE tproductitemstoct.ID_product = L.ID_product) AS NilaiMax
FROM (SELECT ID_product, item,
CASE choice
WHEN 'Y' THEN 1
ELSE 0 END AS choice
FROM tproductitemstoct) AS L
GROUP BY ID_product, item) AS L1) AS L2
GROUP BY L2.ID_product
" onerror="this.style.display='none'"/>
more ...
- Pages:
- 1
- 2
reply |
Report Obsolete
Last Articles
Last Topic
- PascalTalk #6: (Podcast) Kuliah IT di luar negeri, susah gak sih?
by LuriDarmawan in Tutorial & Community Project more 3 years ago - PascalTalk #5: UX: Research, Design and Engineer
by LuriDarmawan in Tutorial & Community Project more 3 years ago - PascalTalk #4: Obrolan Ringan Seputar IT
by LuriDarmawan in Tutorial & Community Project more 3 years ago - PascalTalk #2: Membuat Sendiri SMART HOME
by LuriDarmawan in Tutorial & Community Project more 3 years ago - PascalTalk #3: RADically Fast and Easy Mobile Apps Development with Delphi
by LuriDarmawan in Tutorial & Community Project more 3 years ago - PascalTalk #1: Pemanfaatan Artificial Intelligence di Masa Covid-19
by LuriDarmawan in Tutorial & Community Project more 3 years ago - Tempat Latihan Posting
by LuriDarmawan in OOT more 4 years ago - Archive
- Looping lagi...
by idhiel in Hal umum tentang Pascal Indonesia more 11 years ago - [ask] koneksi ke ODBC user Dsn saat runtime dengan ado
by halimanh in FireBird more 11 years ago - Validasi menggunakan data tanggal
by mas_kofa in Hal umum tentang Pascal Indonesia more 11 years ago
Random Topic
- instalasi zeos
by chandra in Hal umum tentang Pascal Indonesia more 16 years ago - Update Software
by rudyrc in Hal umum tentang Pascal Indonesia more 19 years ago - Error maskEdit
by idhiel in Lain-lain more 11 years ago - tentang olah data dr ms excel di delphi 7 dgn windows vista
by jasonxt in Bedah Kasus more 12 years ago - DBGrid Belang
by unank in Hal umum tentang Pascal Indonesia more 16 years ago - Menampilkan pesan dimonitor lain dengan delphi
by isoke in Hal umum tentang Pascal Indonesia more 18 years ago - Need Help : Mo belajar bikin bel ...
by tox2wow in Multimedia & Graphic Enhancement more 17 years ago - Awas Penipuan
by DelphiExpert in OOT more 16 years ago - baca text file
by sandy in Tip n Trik Pemrograman more 15 years ago - buat Antivirus sendiri
by om20020 in Tip n Trik Pemrograman more 16 years ago