Arsip: Bewara SQL for maniak

 
user image
more 12 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
user image
more 12 years ago

reminder2k1

Ayo temen2 yang bisa, tlg bagi ilmunya... Aku dah nyerah deh... :mrgreen:
user image
more 12 years ago

Manz

Wew ... mabok ... abok ... tapi mendekati nih ... :mrgreen:
user image
more 12 years ago

Manz

sorry ketinggalan messagenya: tinggal splitting doank
user image
more 12 years ago

Manz

lha ... koq edited seh .... yang pertama ajah sqlnya belon nemu ... sekarang dah diganti ke inginan.... wakakaakakakakakak :mrgreen:
user image
more 12 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!
user image
more 12 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
user image
more 12 years ago

ImanD

nah lho, berapa query nih..... di join az bro....... bisa kan 1query, tanpa SP atw function or apalah itu, xixixiix
user image
more 12 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)
user image
more 12 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
Share to
Local Business Directory, Search Engine Submission & SEO Tools FreeWebSubmission.com SonicRun.com